Multi Row Insert

Performing a multi-row insert got easier in SQL 2008. A new syntax was added to accomodate multiple rows when using the VALUES statement. This code will only work in 2008+. In order to accomplish the same thing in earlier versions, you need to use the UNION statement.

Here is the SQL 2008 syntax:

[cc lang=”sql”]
CREATE TABLE #meme
(
id INT IDENTITY(1,1),
first_name VARCHAR(50),
last_name VARCHAR(50)
)

INSERT INTO #meme
( first_name, last_name )
VALUES
(‘john’, ‘smith’),
(‘molly’, ‘jones’),
(‘summer’, ‘fitzgerald’)

SELECT *
FROM #meme
[/cc]

To perform the same thing in earlier versions of SQL Server, you can use the UNION ALL statement:

[cc lang=”sql”]
INSERT INTO #meme
( first_name, last_name )
SELECT ‘john’, ‘smith’
UNION ALL
SELECT ‘molly’, ‘jones’
UNION ALL
SELECT ‘summer’, ‘fitzgerald’

SELECT *
FROM #meme
[/cc]

Remember, you want to use UNION ALL instead of just UNION, otherwise if you specify to rows with the same values to be inserted, it will only insert one — as UNION inherently performs a DISTINCT.

The last way is to simply call the insert statement multiple times.

[cc lang=”sql”]
INSERT INTO #meme
( first_name, last_name )
VALUES (‘john’, ‘smith’);
INSERT INTO #meme
( first_name, last_name )
VALUES (‘molly’, ‘jones’);
INSERT INTO #meme
( first_name, last_name )
VALUES (‘summer’, ‘fitzgerald’);
[/cc]