Are there Table Literals in Transact-SQL?

Question

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?

Answer 1

My understanding is that SELECT * FROM VALUES ... is standard SQL, however, it is not supported by Microsoft's T-SQL. T-SQL, to my knowledge, only supports VALUES ... in INSERT statements. (And even then, only supported multiple values as of SQL Server 2008...)

You can see the grammar of a SELECT statement's FROM clause here: http://msdn.microsoft.com/en-us/library/ms177634%28v=SQL.100%29.aspx

Answer 2

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.