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

Efficiently Inserting Multiple Rows into SQL Tables

Inserting numerous records into a SQL table individually can be incredibly inefficient. Fortunately, SQL Server 2008 and later versions offer a streamlined method for inserting multiple rows using a single INSERT statement. This significantly reduces processing time, especially when dealing with large datasets.

Here's how to achieve this:

1. Leverage the VALUES Clause: The VALUES clause is your key to inserting multiple rows. Within the parentheses following VALUES, list each row's data, separating each row with a comma.

2. Enclose Each Row in Parentheses: Each individual row's data must be enclosed in its own set of parentheses. For instance, inserting four rows into a table with columns Person, Id, and Office would look like this:

<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>

3. Explicitly Define Columns (Best Practice): While not mandatory, explicitly listing the column names (Person, Id, Office in this example) is highly recommended. This ensures data is inserted into the correct columns, preventing potential errors and improving readability.

This method allows for efficient bulk data insertion, saving considerable time and effort compared to individual INSERT statements. It's the preferred approach for handling large-scale data imports into SQL tables.

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