Insert Multiple Rows with One Query or Statement in SQL Server

Here I will explain, many different ways to insert multiple rows with single insert query or statement in SQL Server with example. 

There are most three different methods describe as shown below-

First of all we create a test table (Here my table name is 'tblStudent').

-- create test table 'tblStudent'
CREATE TABLE tblStudent(RollNo INT, Name VARCHAR(100))

Method-1: INSERT Statement
The SQL Server (Transact-SQL) INSERT statement is used to insert a single record or multiple records into a table in SQL Server.

Syntax:
INSERT INTO table
(column1, column2)
VALUES
(expression1, expression2),
(expression1, expression2)

Example:

-- insert multiple rows using INSERT statement
INSERT INTO tblStudent
(RollNo, Name)
VALUES
(100101, 'Harry'),
(100102, 'Michale'),
(100103, 'John'),
(100104, 'Smith')

Method-2: INSERT SELECT Statement

Syntax:
INSERT INTO table1(column1, column2)
SELECT column1, column2 FROM table2

Example:

-- insert multiple rows using INSERT SELECT statement
INSERT INTO tblStudent(RollNo, Name)
SELECT EmpId, EmpName FROM tblEmployee

Method-3: INSERT SELECT UNION ALL Statement

Syntax:
INSERT INTO table(column1, column2)
SELECT expression1, expression2
UNION ALL
SELECT expression1, expression2
UNION ALL
SELECT expression1, expression2

Example:

-- insert multiple rows using INSERT SELECT UNION ALL statement
INSERT INTO tblStudent(RollNo, Name)
SELECT 100101, 'Harry'
UNION ALL
SELECT 100102, 'Michale'
UNION ALL
SELECT 100103, 'John'
UNION ALL
SELECT 100104, 'Smith'

After that, When we run following query in SQL Query window it will return all inserted records with single query like as shown below- 

Result:

-- view all insert data from test table
SELECT * FROM tblStudent