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:

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

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

INSERT INTO #meme
( first_name, last_name )
SELECT 'john', 'smith'
UNION ALL
SELECT 'molly', 'jones'
UNION ALL
SELECT 'summer', 'fitzgerald'

SELECT *
FROM #meme

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.

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');

Featured Articles

 Site Author

  • Thanks for visiting!