How to insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

If you’re using SQL Server 2008 or later, you can insert multiple rows in a single INSERT statement by listing each set of values in parentheses, separated by commas. This approach eliminates the need to repeat INSERT INTO ... for each row.

1. Basic Syntax

INSERT INTO YourTableName (Column1, Column2, Column3)
VALUES 
    (Value1a, Value2a, Value3a),
    (Value1b, Value2b, Value3b),
    (Value1c, Value2c, Value3c);

YourTableName: Name of the table where you want to insert the data.
Column1, Column2, Column3: The target columns in the table.
Value1a, Value2a, etc.: The specific values for each inserted row.

Below is a more concrete example:

INSERT INTO Employees (FirstName, LastName, DepartmentID)
VALUES 
    ('John', 'Doe', 1),
    ('Jane', 'Smith', 2),
    ('Alice', 'Johnson', 3);

In this example, three rows are inserted into the Employees table in one go.

2. Points to Note

SQL Server Version: The multi-row VALUES syntax is fully supported in SQL Server 2008 and later.
Column Consistency: Each row must include the same number of values, matching the number and order of columns specified.
Performance: Inserting multiple rows at once is generally more performant than using individual INSERT statements in a loop or separate batch, especially for large data inserts.
Error Handling: If any single row fails constraints (e.g., unique constraint violation), the entire statement typically rolls back—though you can manage this with TRY/CATCH blocks or other transaction-level logic.

3. Avoiding Repetition of `INSERT INTO`

Prior to SQL Server 2008, inserting multiple rows required multiple INSERT statements or other workarounds (like using a temporary staging table and SELECT from it). Thanks to the enhanced VALUES clause, you can keep your query concise and easy to read.

4. Further SQL Mastery with DesignGurus.io

To refine your SQL skills and master other database essentials, check out these resources:

Grokking SQL for Tech Interviews – Dive into real-world SQL query patterns, optimization techniques, and interview-focused drills.
Grokking Database Fundamentals for Tech Interviews – Learn about normalization, indexing, and schema design, vital for efficient data handling.

You can also explore free tutorials on the DesignGurus.io YouTube channel. If you’re prepping for a high-stakes interview or need personalized guidance, consider their Mock Interviews for feedback from ex-FAANG engineers.

Conclusion

By using the multi-row VALUES syntax, you can insert several rows in one statement without duplicating INSERT INTO multiple times. This approach is cleaner, more efficient, and supported in SQL Server versions starting from 2008.

CONTRIBUTOR