THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

2008: More Row Constructor Fun

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:

    MovieID INT IDENTITY(1, 1),
    MovieRatingId INT,
    Title VARCHAR(200) NOT NULL

INSERT INTO @Movie (MovieRatingId, Title)
    (3, 'SQL the Movie'),
    (4, 'SQL Massacre'),
    (1, 'SQL for Everyone'),
    (4, 'SQL Massacre 2 - The Oracle Returns');

WITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)
(    SELECT *
        (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)
    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
    (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.


Published Wednesday, December 5, 2007 10:32 PM by Peter W. DeBetta



Denis Gobo said:

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

December 6, 2007 11:55 AM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement