Insert Multiple Records in One Transaction

If you want to insert several rows into a table in one transaction, you can use the Select into statement with a combination of a select union statement. For an example, you can look at the following sample:

INSERT INTO table1(col1, col2)

SELECT 'one' ,1

UNION ALL

SELECT 'two' ,2

UNION ALL

SELECT 'three' ,3

The union statements result in three rows which are returned with two columns. The result set is then inserted as one transaction into table1. If any of the rows has any constraint violations, the entire transaction is not committed.

If you are using SQL Server 2008, you can use this syntax:

INSERT INTO table1(col1, col2)

values ('one' ,1),

       ('two' ,'osc'),

       ('three' ,3)

That also allows us to insert multiple records without using a union statement. If any of the values fails, none of the rows are inserted.