Skip to Content

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!

One Comment

  • I decided to do a quick post In addition to the posts by Peter and Louis . And yes I copied Louis’ table

Receive comment updates via RSS

Leave a Comment