Using Table Value Constructors aka Row Constructors in SQL Server 2008 to insert multiple rows using a single statement

Although its been a while since SQL Server 2008 has been released, I see developers working on SQL Server 2008 inserting data in a table ‘the 'old way’ like this:

DECLARE @TT TABLE (ID int, Name varchar(10))
INSERT INTO @TT (ID, Name) VALUES (1, 'Jack')
INSERT INTO @TT (ID, Name) VALUES (2, 'Jill')
INSERT INTO @TT (ID, Name) VALUES (3, 'Hill')
INSERT INTO @TT (ID, Name) VALUES (4, 'Bill')

If you are using SQL Server 2008, then you must use the Table Value Constructors (earlier known as Row Constructors) wherever you can to populate data. So the same code can be rewritten using Table Value Constructors as:

DECLARE @TT TABLE (ID int, Name varchar(10))
INSERT INTO @TT (ID, Name)
VALUES (1, 'Jack'),
       (2, 'Jill'),
       (3, 'Hill'),
       (4, 'Bill')

Check the syntax! Simple, readable and less typing!

Note: You can construct a maximum of 1000 rows using this syntax. To insert more than 1000 rows, use multiple inserts, each containing 1000 rows at a time.

Read more about Table Value Constructors over here http://msdn.microsoft.com/en-us/library/dd776382.aspx

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: