April 24, 2010

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?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

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

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions