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.
The syntax for inserting multiple rows in SQL Server is as follows:
INSERT INTO MyTable (column1, column2, column3)
(value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);
In this syntax, the
MyTable table has three columns: Column1, Column2, and Column3.
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.
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:
insert into Customers(id,Name,City)
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:
insert into Students_Math(id,Name,City)
select ID, Name, City from Students where id in (3,4);
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.
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.
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.