RSS Feed

Insert Multiple Values in Table

How to insert multiple values in table using only one insert?

Usually what we do is

USE Your_DB
GO
INSERT INTO DummyTable (Col_1, Col_2)
VALUES ('First',1);
INSERT INTO DummyTable (Col_1, Col_2)
VALUES ('Second',2);
INSERT INTO DummyTable (Col_1, Col_2)
VALUES ('Third',3);

GO

Above the clause INSERT INTO is repeated many times. Another way is to use UNION ALL and INSERT INTO … SELECT… clauses.

USE Your_DB
GO
INSERT INTO DummyTable (Col_1, Col_2)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3

GO