␡
- Initializing and Incrementing Variables
- Inserting Multiple Rows with a Single Statement
- Merging Multiple Data Sets
- Separate Date and Time Data Types
- New Date- and Time-Related Functions
Like this article? We recommend
Inserting Multiple Rows with a Single Statement
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Inserting Multiple Rows with a Single Statement
The INSERT command now allows for specifying multiple sets of values so that each transaction can append more than one row of data to the table. To illustrate, I'll create a temporary table and add several rows to it in just two statements:
CREATE TABLE #dropme_rowconstructor_sample (
key_column INT,
name_column VARCHAR(30)
);
INSERT #dropme_rowconstructor_sample (key_column, name_column)
VALUES
(1, 'Abby'),
(2, 'Becky'),
(3, 'Chloe'),
(4, 'Debby'),
(5, 'Ella')
You can even use sub-queries to construct the VALUES clause used in the INSERT statement, like this:
INSERT #dropme_rowconstructor_sample (
key_column,
name_column)
VALUES
(6,
(SELECT TOP 1 name_column FROM #dropme_rowconstructor_sample)
)
In previous versions of SQL Server, we would have to write a separate INSERT statement for adding each row, or do much more typing using UNION ALL syntax to append multiple rows in a single transaction. Now we can add up to 1,000 rows to a table by using a single INSERT command with row constructors.