Menu
According to http://www.storytotell.org/blog/2008/11/14/literal-tables-and-updates-with-joins-in-sql.html
the following is valid:
SELECT * FROM VALUES ('Lisp', 50, true), ('Scheme', 30, true), ('Clojure', 1, true) AS languages (name, age, lispy) But it doesn't appear to work.
The best i can get is
With languages (name, age, lispy) as ( select 'Lisp', 50, 'true' union all select 'Scheme', 30, 'true' union all select 'Clojure', 1, 'true' ) select * from languages which uses a common table expression and is not quite as neat.
Is there anything like a table literal in t-sql?
842
Literal SQL - SSIS. Literals are hard coded information that you must provide when building expressions. SSIS expressions have three types of literals: numeric, string, and Boolean. literal overflows the type.
You can either loop through the rows with a cursor and append to a field in a temp table, or you could use the COALESCE function to concatenate the fields.
To declare a table variable, start the DECLARE statement. The name of table variable must start with at(@) sign. The TABLE keyword defines that used variable is a table variable. After the TABLE keyword, define column names and datatypes of the table variable in SQL Server.
If you have SQL Server 2008, you can use it anywhere a derived table is allowed, although it only lets you have up to 1000 rows: http://msdn.microsoft.com/en-us/library/dd776382(SQL.100).aspx
Here's an example from the documentation ( http://msdn.microsoft.com/en-us/library/ms177634(SQL.100).aspx ):
SELECT * FROM ( VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b) Note the parentheses around the VALUES clause.
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
