Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Inserting Multiple Rows with a Single Statement

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.

  • + Share This
  • 🔖 Save To Your Account