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);

2 replies
  1. Plamen Ratchev
    Plamen Ratchev says:

    You can use CAST with desired data type:

    ;WITH x AS (
    SELECT CAST(1 AS DECIMAL(10, 2)) AS col1,
    CAST(N'a' AS NVARCHAR(10)) AS col2)
    SELECT col1, col2
    INTO tmp
    FROM x;

    EXEC sp_help N'tmp';

    DROP TABLE tmp;

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.