Insert Multiple Records Using One Insert Statement

Transact-SQL gained some enhancements with SQL Server 2008 and later versions. One of the improvements is the ability to insert a number of rows of data with a single INSERT statement, without executing a query to generate the new information. INSERT Statement

When using Transact-SQL (T-SQL) to add rows to a table using an INSERT statement before SQL Server 2008, you were able to either add a single row of data or insert multiple rows if they were the result of a query. If you wanted to insert a number of rows of raw data, you would need to execute multiple INSERT statements or use the UNION ALL trick.

From SQL Server 2008, Microsoft added the ability to insert more than one row with a single INSERT statement and without using a union operation. The syntax for the statement is similar to that for creating one row. Normally you would use the VALUES clause, followed by a single row's worth of data surrounded by parentheses. To insert more than one row, you need only to provide multiple sets of data, each in parentheses and separated by commas.

For example, the following command inserts four rows into the Salespeople table:

INSERT INTO Salespeople
    (Name, Area, SalesTarget)
VALUES
    ('Bob', 'East', 100000),
    ('Jim', 'West', 120000),
    ('Mel', 'North', 110000),
    ('Sue', 'Central', 120000)

SQL TSQL T-SQL insert insert into select