Insert Multiple Rows with Single INSERT statement in SQL Server

Today, I am going to give you simple and efficient TIP to insert multiple records with single INSERT statement.
--Lets Create a Dummy table
CREATE TABLE EMPLOYEE(
EMP_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
MIDLE_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
SALARY BIGINT,
DEPARTMENT_ID INT
)
-- Single Insert statement with multiple Value Clauses
INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Vijaya','Krishna','Kadiyala',20000,10),
('Namrath',Null,'Kadiyala',40000,10),
('Glen',Null,'Jhonson',35000,20),
('Ray','Fine','Muran',21000,20)

That’s it.

If we didn’t had this feature then we had to use following techniques to do the same.

Using Multiple Insert statements:

INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Vijaya','Krishna','Kadiyala',20000,10);

INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Namrath',Null,'Kadiyala',40000,10);

INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Glen',Null,'Jhonson',35000,20);

INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
Values ('Ray','Fine','Muran',21000,20);

Using SELECT statement with Union All:

INSERT INTO Employee(FIRST_NAME,MIDLE_NAME,LAST_NAME,SALARY,DEPARTMENT_ID)
SELECT 'Vijaya','Krishna','Kadiyala',20000,10
UNION ALL
SELECT 'Namrath',Null,'Kadiyala',40000,10
UNION ALL
SELECT 'Glen',Null,'Jhonson',35000,20
UNION ALL
SELECT 'Ray','Fine','Muran',21000,20