My esteemed colleague Louis Davidson just wrote about a new feature in SQL Server 2008 known as row constructors. I thought I'd follow up with some more fun usage of this cool feature. In addition to being used with an INSERT statement, you can also use a row constructor in a common table expression (CTE). This example uses the row constructor to insert data into one table (@Movie) and then again to join a table to another sets of constructed row:
DECLARE @Movie TABLE
(
MovieID INT IDENTITY(1, 1),
MovieRatingId INT,
Title VARCHAR(200) NOT NULL
);
INSERT INTO @Movie (MovieRatingId, Title)
VALUES
(3, 'SQL the Movie'),
(4, 'SQL Massacre'),
(1, 'SQL for Everyone'),
(4, 'SQL Massacre 2 - The Oracle Returns');
WITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)
AS
( SELECT *
FROM (VALUES
(0, 'UR','Unrated',1),
(1, 'G','General Audiences',1),
(2, 'PG','Parental Guidance',1),
(3, 'PG-13','Parental Guidance for Children Under 13',1),
(4, 'R','Restricted, No Children Under 17 without Parent',0))
AS MR(MovieRatingId, Code, Description, AllowYouthRentalFlag)
)
SELECT
M.Title,
MR.Code AS RatingCode,
MR.Description AS RatingDescription
FROM @Movie AS M
INNER JOIN MovieRatings AS MR
ON M.MovieRatingId = MR.MovieRatingId;
Notice that when used in a CTE, the syntax is slightly different
SELECT column_list
FROM
(VALUES
(column_1_value [, column_2_value [, ...]])
(column_1_value [, column_2_value [, ...]])
) AS TableAlias (column_1_name [, column_2_name [, ...]])
When used in a CTE, you must supply a table alias, as well as column names for all columns supplied by the row constructor.
Enjoy!
Cross-posted from SQLBlog! -
http://www.sqlblog.com