How Can I Insert Multiple Rows into a SQL Server Table with a Single Query?

Efficiently Inserting Multiple Rows into SQL Server

Need to add multiple data entries to your SQL Server table without writing multiple INSERT statements? This guide demonstrates how to insert multiple rows using a single SQL query, a highly efficient method, especially for large datasets or when transactional integrity is crucial.

SQL Server (2008 and later) offers a concise syntax for this:

<code class="language-sql">INSERT INTO MyTable (Column1, Column2, Column3) VALUES
(Value1, Value2, Value3), (Value1, Value2, Value3), ...</code>

Let's illustrate with an example. Assume you have a MyTable with columns Person, Id, and Office. To insert four rows, the following single query suffices:

<code class="language-sql">INSERT INTO MyTable (Person, Id, Office) VALUES
('John', 123, 'Lloyds Office'),
('Jane', 124, 'Lloyds Office'),
('Billy', 125, 'London Office'),
('Miranda', 126, 'Bristol Office');</code>

This approach inserts all four rows within a single transaction, ensuring data consistency. Explicitly listing the columns enhances readability and safeguards data integrity.

The above is the detailed content of How Can I Insert Multiple Rows into a SQL Server Table with a Single Query?. For more information, please follow other related articles on the PHP Chinese website!