Table Value Constructors in SQL Server 2008

One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them.

-- Populate sample table

 

CREATE TABLE Foo (

  keycol INT PRIMARY KEY,

  datacol VARCHAR(30));


In the past, populating table rows was done like this:

INSERT INTO Foo VALUES (1, 'Books');

INSERT INTO Foo VALUES (2, 'CDs');

INSERT INTO Foo VALUES (3, 'DVDs');

 

-- or

 

INSERT INTO Foo (keycol, datacol)

SELECT 1, 'Books'

UNION ALL

SELECT 2, 'CDs'

UNION ALL

SELECT 3, 'DVDs';

 

-- or using on the fly

 

SELECT keycol, datacol

FROM ( SELECT 1, 'Books'

       UNION ALL

       SELECT 2, 'CDs'

       UNION ALL

       SELECT 3, 'DVDs') AS Foo (keycol, datacol);


Here is how the same can be done with SQL Server 2008 table value constructors:

INSERT INTO Foo (keycol, datacol)

VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');

 

-- or using on the fly

 

SELECT keycol, datacol

FROM ( VALUES (1, 'Books'),

              (2, 'CDs'),

              (3, 'DVDs') ) AS Foo (keycol, datacol);

 

-- and CTE version

 

WITH Foo (keycol, datacol)

AS( SELECT *

    FROM VALUES (1, 'Books'),

                    (2, 'CDs'),

                    (3, 'DVDs') ) AS F (keycol, datacol))

SELECT keycol, datacol

FROM Foo;


Another interesting option is to derive a row value from a subquery, like this:

INSERT INTO Foo (keycol, datacol)

VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');


Still not possible, but maybe in the next version (or Service Pack) we can see vector expressions in predicates and UPDATE, like:

SELECT keycol, datacol

FROM Foo

WHERE (keycol, datacol) IN (SELECT keycol, datacol FROM Foo2);

 

-- or

 

UPDATE Foo

SET (keycol, datacol) = (SELECT keycol, datacol FROM Foo2);

Labels: ,