1

Most other databases allow you to generate table values using VALUES. As far as I can tell, SQL Server lets you do this with subqueries:

SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
) as sq(givenname,familyname,email);

but I can’t appear to get it working with CTEs. The best I can do is embed a subquery inside a CTE:

WITH data AS (
    SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
    ) as sq(givenname,familyname,email)
)
SELECT * FROM data;

I prefer CTEs because of their readability, but is there a more direct way of doing this? For example PostgreSQL, MariaDB (supporting versions) and SQLite all accept this:

WITH data(givenname,familyname,email) AS (
    VALUES
        ('Cornelius','Eversoe', 'cornelius@example.net'),
        ('Ebenezer','Splodge', 'ebenezer@example.net'),
        ('Sylvester','Underbar', 'sylvester@example.net'),
        ('Cynthia','Hyphen-Smythe', 'cynthia@example.net')
)
SELECT * FROM data;
2

No, SQL Server only currently supports table-valued constructors in a derived table (or the VALUES clause of an INSERT ... VALUES statement). So, you can use VALUES in a join (including apply) or derived table ("subquery") but not a Common Table Expression.

Sadly, T-SQL is lagging in several areas compared with other popular SQL databases, for example Add support for ANSI standard row value constructors.

| improve this answer | |
  • 1
    I love the response: Nov 13 2007 — “We’re certainly considering row value constructors for a future release of SQL Server”. Is that the same future version requiring semicolons? Nobody would accuse Microsoft of being over hasty to implement standards. – Manngo Dec 5 '19 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.