In SQL Server, it is often necessary to insert multiple rows into a table at once. Inserting multiple rows in SQL Server can be achieved using the INSERT INTO statement with the VALUES clause. This statement allows you to insert data into a table or view.

Syntax

The syntax for inserting multiple rows in SQL Server is as follows:

INSERT INTO MyTable (column1, column2, column3)
VALUES
(value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);

In this syntax, the MyTable table has three columns: Column1, Column2, and Column3.
The VALUES clause specifies three sets of values, each set enclosed in parentheses and separated by commas. Each set of values represents one row to be inserted into the table.

Note that the order of the values in each set must match the order of the columns in the table. Also, make sure that any string values are enclosed in single quotes.

Examples

Insert multiple rows using single insert statement

For example, let's say we have a table called Customers with columns ID, Name, and City. To insert multiple rows into this table, we can use the following SQL statement:

USE model;
GO
insert into Customers(id,Name,City)
values
(1,'William','New York'),
(2,'Donna','San Jose'),
(3,'Michelle','Boston'),
(4,'Jason','San Antonio')
GO

This statement inserts four rows into the Customers table with the specified values for each column. Note that the number of values in each row must match the number of columns in the table.

Insert multiple rows using select statement

You can also insert multiple rows into a table using a SELECT statement. Here's an example:

USE model;
GO
insert into Students_Math(id,Name,City)
select ID, Name, City from Students where id in (3,4);
GO

Insert multiple rows using select union all

INSERT INTO MyTable (Column1, Column2, Column3)
SELECT 'Value1', 'Value2', 'Value3'
UNION ALL SELECT 'Value4', 'Value5', 'Value6'
UNION ALL SELECT 'Value7', 'Value8', 'Value9';

In this example, the SELECT statement generates three rows of data, which are then inserted into the MyTable table using the INSERT INTO statement.

The UNION ALL operator combines the results of three SELECT statements into a single result set. Each SELECT statement specifies a set of values to be inserted into the table, with the column names specified in the SELECT list.

In conclusion, inserting multiple rows in SQL Server can be done using the INSERT INTO statement with the VALUES clause or a SELECT statement. The VALUES clause is useful for inserting a small number of rows, while the SELECT statement is useful for inserting a large number of rows or for inserting rows from another table.