Inserting Multiple Rows in MSSQL

While trying to find a way to insert multiple rows of data in one query using MSSQL, I found a solution on SQLAuthority.com. Their solution involves making use of the UNION ALL statement to combine multiple value statements. The example they give is:

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

This is a great solution and works perfectly. However, I have found that it is not the easiest to remember – I have to go back to that site any time I want to use this because I just can’t remember the exact syntax. I was discussing this solution with a co-worker and he suggested we try something different:

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES
('First', 1),
('Second', 2),
('Third', 3),
('Fourth', 4),
('Fifth', 5);

Sure enough, it works! Not only is it easier to write, but it’s so much easier to remember. Hopefully others will find this as useful as I did.