The INSERT INTO SELECT statement in SQL Server allows you to insert rows into a table based on the result of a SELECT query. This is particularly useful when you want to copy data from one table to another, or populate a table with the results of a query.
Basic Syntax of INSERT INTO SELECT
There are two main forms of the INSERT INTO SELECT statement:
Syntax:
Insert into an existing table:
INSERT INTO target_table (column1, column2, …)
SELECT column1, column2, …
FROM source_table
WHERE condition;# Insert into an existing table without specifying the columns (assuming the tables have the same structure):
INSERT INTO target_table
SELECT column1, column2, …
FROM source_table
WHERE condition;
Example : Copying Data from One Table to Another
Assume we have two tables, Products and ProductsArchive, with the same structure:
#We want to copy all records from Products to ProductsArchive.
INSERT INTO ProductsArchive (ProductID, ProductName, Price, Quantity)
SELECT ProductID, ProductName, Price, Quantity
FROM Products;# Example : Inserting Data with a Condition
We can use a WHERE clause to insert only specific rows. For instance, we want to insert products with a Price greater than 1.00 into ProductsArchive.INSERT INTO ProductsArchive (ProductID, ProductName, Price, Quantity)
SELECT ProductID, ProductName, Price, Quantity
FROM Products
WHERE Price > 1.00;# Example : Inserting Data with a Computed Column
You can also compute values while selecting data to insert. Suppose we want to insert products into ProductsArchive and compute a TotalValue column.INSERT INTO ProductsArchive (ProductID, ProductName, Price, Quantity, TotalValue)
SELECT ProductID, ProductName, Price, Quantity, Price * Quantity AS TotalValue
FROM Products;
Example : Inserting Data from Multiple Tables
You can use joins in your SELECT statement to insert data from multiple tables. Assume we have an Orders table and want to insert data into OrderDetails table.
We want to insert orders with their details into OrdersArchive.
INSERT INTO OrdersArchive (OrderID, CustomerID, OrderDate, ProductID, Quantity)
SELECT o.OrderID, o.CustomerID, o.OrderDate, od.ProductID, od.Quantity
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID;
Handling Identity Columns
When dealing with tables that have identity columns (auto-incrementing columns), you might need to enable IDENTITY_INSERT to explicitly insert values into these columns.
Example:
# SET IDENTITY_INSERT ProductsArchive ON;
INSERT INTO ProductsArchive (ProductID, ProductName, Price, Quantity)
SELECT ProductID, ProductName, Price, Quantity
FROM Products;
Error Handling
It’s important to handle potential errors, such as attempting to insert duplicate keys or violating constraints. Using TRY…CATCH blocks can help manage these errors.
# Example
BEGIN TRY
INSERT INTO ProductsArchive (ProductID, ProductName, Price, Quantity)
SELECT ProductID, ProductName, Price, Quantity
FROM Products;
END TRY
BEGIN CATCH
PRINT ‘Error occurred: ‘ + ERROR_MESSAGE();
END CATCH;
This tutorial provides an overview of the INSERT INTO SELECT statement with practical examples. Practice these examples and experiment with your own scenarios to become proficient in using this SQL statement.