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


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

No comments: