As a developer, one of the common tasks you may encounter is inserting multiple rows into a single table in a single statement. Writing multiple insert statements can be tedious and time-consuming. Fortunately, there are several methods available in SQL Server to simplify this process.

Method 1: Traditional Method of INSERT…VALUES

The traditional method involves writing separate insert statements for each row you want to insert. Here’s an example:

CREATE TABLE #SQLAuthority (
    ID INT,
    Value VARCHAR(100)
);

INSERT INTO #SQLAuthority (ID, Value) VALUES (1, 'First');
INSERT INTO #SQLAuthority (ID, Value) VALUES (2, 'Second');
INSERT INTO #SQLAuthority (ID, Value) VALUES (3, 'Third');

-- Clean up
TRUNCATE TABLE #SQLAuthority;

This method is straightforward but can become repetitive and cumbersome when dealing with a large number of rows.

Method 2: INSERT…SELECT

An alternative method is to use the INSERT…SELECT statement with UNION ALL. This allows you to select multiple rows from a source table or use literal values. Here’s an example:

INSERT INTO #SQLAuthority (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';

-- Clean up
TRUNCATE TABLE #SQLAuthority;

This method eliminates the need for multiple insert statements and provides a more concise way to insert multiple rows.

Method 3: SQL Server 2008+ Row Construction

If you’re using SQL Server 2008 or later, you can take advantage of the row constructor feature. This allows you to specify multiple rows within a single INSERT statement. Here’s an example:

INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');

-- Clean up
DROP TABLE #SQLAuthority;

This method provides a compact and efficient way to insert multiple rows in a single statement.

By using these methods, you can save time and effort when inserting multiple rows into a SQL Server table. Choose the method that best suits your needs and enjoy a more streamlined development process.

Do you have any other SQL Server topics you’d like us to cover? Share your ideas with us, and we’ll do our best to provide educational material on those topics.