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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook