Insert Multiple Rows in SQL Server

Understanding the Concept:

• When inserting multiple rows into a table, you typically need to repeat the INSERT INTO ... statement for each row. This can become cumbersome, especially for large datasets.
• To streamline this process, SQL Server provides a technique that allows you to specify multiple rows within a single INSERT INTO statement. This is achieved using a VALUES clause.

Syntax and Example:

The basic syntax for inserting multiple rows using the VALUES clause is as follows:

INSERT INTO your_table_name (column1, column2, ...)
VALUES (value1, value2, ...),
      (value1, value2, ...),
      ...;

Here's a practical example:

INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES (1, 'John Doe', 'New York'),
      (2, 'Jane Smith', 'Los Angeles'),
      (3, 'Michael Johnson', 'Chicago');

In this example:

• The Customers table has columns named CustomerID, CustomerName, and City.
• Three sets of values are provided within the VALUES clause, each representing a row to be inserted.

Key Points:

• The number of values in each set must match the number of columns specified in the INSERT INTO statement.
• You can insert as many rows as needed within a single VALUES clause.
• The VALUES clause can be used with any SQL Server version, including SQL Server 2005.

Additional Considerations:

• For very large datasets, consider using bulk insert methods like BULK INSERT or SSIS to improve performance.
• If you need to insert rows from a temporary table or another query result set, use a SELECT statement within the VALUES clause.

Inserting Multiple Rows Without Repeating INSERT INTO

Using the VALUES Clause:

This is the most common and straightforward method. You list multiple sets of values within a single INSERT INTO statement, separated by commas.

INSERT INTO your_table_name (column1, column2, ...)
VALUES (value1, value2, ...),
      (value1, value2, ...),
      ...;

Example:

INSERT INTO Customers (CustomerID, CustomerName, City)
VALUES (1, 'John Doe', 'New York'),
      (2, 'Jane Smith', 'Los Angeles'),
      (3, 'Michael Johnson', 'Chicago');

Using a SELECT Statement:

If you have data in another table or query result, you can use a SELECT statement to insert multiple rows.

INSERT INTO your_table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM your_other_table;

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
SELECT MAX(OrderID) + 1, CustomerID, GETDATE()
FROM Customers;

Inserting Multiple Rows in SQL Server

Using a Stored Procedure:

For complex operations or repeated insertions, a stored procedure can be used.

CREATE PROCEDURE InsertMultipleRows
AS
BEGIN
    INSERT INTO your_table_name (column1, column2, ...)
    VALUES (value1, value2, ...),
          (value1, value2, ...),
          ...;
END;

EXEC InsertMultipleRows;

Using a Temporary Table:

If you need to process or manipulate data before insertion, a temporary table can be used.

CREATE TABLE #TempTable (
    column1 type,
    column2 type,
    ...
);

-- Insert data into the temporary table

INSERT INTO your_table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM #TempTable;

DROP TABLE #TempTable;

CREATE TABLE #CustomerData (
    CustomerID INT,
    CustomerName VARCHAR(50),
    City VARCHAR(50)
);

INSERT INTO #CustomerData VALUES
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'Los Angeles');

INSERT INTO Customers (CustomerID, CustomerName, City)
SELECT CustomerID, CustomerName, City
FROM #CustomerData;

DROP TABLE #CustomerData;

Alternative Methods for Inserting Multiple Rows in SQL Server

While the VALUES clause is a common and effective method, there are other alternatives depending on your specific requirements:

Bulk Insert:

• Purpose: Efficiently inserting large datasets from a file.
• Syntax:

  BULK INSERT your_table_name
  FROM 'path_to_file'
  WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      FIRSTROW = 2
  );
  

SSIS (SQL Server Integration Services):

• Purpose: Creating data integration solutions, including bulk data loading.
• Process:
  1. Create an SSIS package.
  2. Add a Data Flow task.
  3. Use a Flat File Source to read data from a file.
  4. Use an OLE DB Destination to insert data into a SQL Server table.

Stored Procedure with Dynamic SQL:

• Purpose: Creating a flexible procedure that can insert different numbers of rows or columns.
• Syntax:

  CREATE PROCEDURE InsertMultipleRows
  (
      @values NVARCHAR(MAX)
  )
  AS
  BEGIN
      DECLARE @sql NVARCHAR(MAX) = 'INSERT INTO your_table_name (column1, column2, ...) VALUES (' + @values + ')';
      EXEC sp_executesql @sql;
  END;
  

• Example:

  EXEC InsertMultipleRows '("John Doe", "New York")';
  

Using a CTE (Common Table Expression):

• Purpose: Creating temporary result sets that can be used in other queries.
• Syntax:

  WITH CTE AS (
      SELECT column1, column2, ...
      FROM your_table
  )
  INSERT INTO your_other_table (column1, column2, ...)
  SELECT column1, column2, ...
  FROM CTE;
  

• Example:

  WITH SelectedCustomers AS (
      SELECT CustomerID, CustomerName
      FROM Customers
      WHERE City = 'New York'
  )
  INSERT INTO Orders (CustomerID, OrderDate)
  SELECT CustomerID, GETDATE()
  FROM SelectedCustomers;