Table Valued Constructors: insert multiple rows of hard-coded values into a table

This one I borrowed from Plamen Ratchev:
http://pratchev.blogspot.nl/2008/01/table-value-constructors-in-sql-server.html

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


The big advantage of TVCs is that you save typing 🙂








Advertisements

One thought on “Table Valued Constructors: insert multiple rows of hard-coded values into a table

  1. Pingback: Clint Huijbers' Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s