In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at the Table Value Constructor, sometimes also called Row Value Constructor. The Table Value Constructor allows multiple rows of data to be specified in a single DML statement. Instead of doing the following

INSERT TABLE
SELECT 3, '20020101','B' UNION ALL 
SELECT 5, '20030101','C' UNION ALL 
SELECT 7, '20040101','D'

you can do this

INSERT TABLE
VALUES
(3, '20020101','B'), 
(5, '20030101','C'), 
(7, '20040101','D')

Here are some limitations as described in Books On Line

The maximum number of rows that can be constructed using the table value constructor is 1000. To insert more than 1000 rows, create multiple INSERT statements, or bulk import the data by using the bcp utility or the BULK INSERT statement.

Only single scalar values are allowed as a row value expression. A subquery that involves multiple columns is not allowed as a row value expression

Let’s get started, first create this simple table

CREATE TABLE Test(ID int,SomeDate date,SomeChar char(1))
GO

Here is how to do a simple insert

INSERT Test
VALUES  (1, '20010101','A'),
		(3, '20020101','B'), 
		(5, '20030101','C'), 
		(7, '20040101','D'), 
		(9, '20050101','E')
GO

Now if we check the table

SELECT * FROM Test

ID	SomeDate	SomeChar
--  ----------  --------
1	2001-01-01	A
3	2002-01-01	B
5	2003-01-01	C
7	2004-01-01	D
9	2005-01-01	E

We can see we have 5 rows. You can also use queries instead of hard coding values, below is an example

INSERT Test
VALUES  (1, '20010101','A'),
		(3, '20020101','B'), 
		(5, '20030101','C'), 
		(7, '20040101','D'), 
		(9, '20050101','E'),
		((SELECT id FROM Test where id = 1),
				(SELECT SomeDAte FROM Test where id = 1),
				(SELECT SomeChar FROM Test where id = 1))

Now, we should have 3 rows with the value 1 for ID

SELECT * FROM Test 
WHERE id = 1

ID  SomeDate	SomeChar
--  ----------  --------
1   2001-01-01	A
1   2001-01-01	A
1   2001-01-01	A

Remember the warning from before: A subquery that involves multiple columns is not allowed as a row value expression?

If you try to run the same insert query, you will get the error message below, this is because the subquery now returns more than 1 value for the rows where the value for ID is 1

_Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated._

Besides using Table Value Constructor for inserts, you can also use them for selects

The following UNION query can be rewritten to be a Table Value Constructor

SELECT 1 as a, '20010101' as b,'A' as c UNION ALL
SELECT 3, '20020101','B' UNION ALL 
SELECT 5, '20030101','C' UNION ALL 
SELECT 7, '20040101','D' UNION ALL 
SELECT 9, '20050101','E'
GO

Here is what the Table Value Constructor version looks like, I think it is a little neater

SELECT *
FROM (VALUES
		(1, '20010101','A'),
		(3, '20020101','B'), 
		(5, '20030101','C'), 
		(7, '20040101','D'), 
		(9, '20050101','E') ) AS SomeTable(a, b,c);
GO

a   b		c
--  --------    --
1   20010101	A
3   20020101	B
5   20030101	C
7   20040101	D
9   20050101	E

That is all for today, come back tomorrow for part 13