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: ,