THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

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!

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement