AzureFeeds

Prior to SQL Server 2008, we could only insert a single row at a time. SQL Server 2008 added the ability to have multiple rows of data in the VALUES clause.

Each row is created from what are typically called row constructors. In the example above, I’m inserting 3 rows into the Cinema Groups table. The syntax allows for up to 1000 rows to be inserted at once.

Before you get to that number of rows though, you might exceed the maximum allowable length for a T-SQL statement, if you have a lot of columns, each with a lot of data. The maximum length of a statement is in theory 2GB but most people run into the batch size limit of 65536 * network packet size or about 268MB, first. Either way, it’s a lot of data in a single statement, and running into these limits is uncommon.

Atomic Statement

Even with 3 rows being inserted, SQL Server treats this as what’s called an atomic statement. That means the entire operation works or it doesn’t work. So if I do this insert, and I’ve got 3 rows there, and one row doesn’t work, it means that the other rows won’t be inserted either.

All happen or none happen.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #129 Inserting Multiple Rows at Once with VALUES clause in SQL Server T-SQL appeared first on The Bit Bucket.