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;