Insert Multiple Records Using One Insert Statement

When there are multiple records are to be inserted in the table following is the common way using T-SQL.

USE [DatabaseName]
GO
INSERT INTO MyTable  (name, id)
       
VALUES ('ankit',1);
INSERT INTO MyTable  (name, id)
       
VALUES ('rohit',2);
INSERT INTO MyTable  (name, id)
       
VALUES ('shashi',3);
INSERT INTO MyTable  (name, id)
       
VALUES ('ritesh',4);
GO

The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses.

Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. He was quite pleased
.

USE [DatabaseName]
GO
INSERT INTO MyTable (name, id)
SELECT 'ankit' ,1
UNION ALL
SELECT 'rohit' ,2
UNION ALL
SELECT 'shashi' ,3
UNION ALL
SELECT 'ritesh' ,4
GO