The SQL INSERT INTO SELECT statement is a powerful tool in SQL that allows you to copy data from one table and insert it into another. This operation is fundamental for tasks like data migration, creating backups, or populating staging tables. It’s a streamlined approach to data manipulation, especially when you need to transfer data based on specific criteria or transform it in the process.
It’s important to note that when using INSERT INTO SELECT, the data types of the source and target tables must be compatible. SQL Server will perform implicit conversions where possible, but mismatches can lead to errors or data truncation. Also, the existing records in the target table remain untouched; the INSERT INTO SELECT statement only adds new rows.
Understanding the Syntax
The INSERT INTO SELECT statement has two primary syntax structures, catering to different needs:
1. Copying all columns from one table to another:
INSERT INTO table2
SELECT *
FROM table1
WHERE condition;In this syntax:
table2is the target table where the data will be inserted. It must already exist.table1is the source table from which data will be copied.SELECT *specifies that all columns fromtable1will be selected and inserted.WHERE conditionis optional and allows you to filter the rows fromtable1that will be copied.
2. Copying specific columns from one table into another:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;Here:
table2 (column1, column2, column3, ...)explicitly lists the columns in the target table that will receive data.SELECT column1, column2, column3, ...specifies the corresponding columns from the source table (table1) to be copied. The order and number of columns in both lists must match.WHERE conditionagain provides an optional filter for selecting specific rows fromtable1.
Exploring with the Northwind Database
To illustrate the practical application of INSERT INTO SELECT, we’ll use the widely recognized Northwind sample database. This database is excellent for learning SQL because it represents a typical business scenario with related tables like Customers and Suppliers.
Let’s examine snippets from the “Customers” and “Suppliers” tables:
Customers Table (Partial View):
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
Suppliers Table (Partial View):
| SupplierID | SupplierName | ContactName | Address | City | Postal Code | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly’s Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
Practical Examples of INSERT INTO SELECT
Let’s dive into some examples to see INSERT INTO SELECT in action.
Example 1: Copying Suppliers to Customers (Partial Columns)
Imagine we want to create a simplified list of customers based on our supplier data, focusing on CustomerName, City, and Country. Columns in the Customers table that are not specified in the INSERT INTO statement will be populated with NULL if the column allows nulls, or default values if defined.
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country
FROM Suppliers;This SQL statement will insert new customer records into the Customers table. The CustomerName, City, and Country columns will be populated with data from the SupplierName, City, and Country columns of the Suppliers table, respectively. Columns like ContactName, Address, and PostalCode in the Customers table will be left as NULL (assuming they are nullable).
Example 2: Copying Suppliers to Customers (All Matching Columns)
If we intend to copy more supplier information into the Customers table, and we want to fill columns that have similar meanings, we can map them explicitly. Let’s assume PostalCode in Customers corresponds to Postal Code in Suppliers.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, `Postal Code`, Country
FROM Suppliers;In this case, we are explicitly mapping columns from Suppliers to corresponding columns in Customers. This provides more control and ensures that relevant data is transferred to the correct columns in the target table. Note that if the Customers table doesn’t have columns that directly correspond to all columns in Suppliers, you would select only the columns you need and ensure the target table structure is appropriate.
Example 3: Conditional Copying – German Suppliers to Customers
Often, you need to copy data based on specific criteria. For instance, let’s say we only want to add German suppliers to our Customers table. We can use a WHERE clause to filter the data during the selection process.
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country
FROM Suppliers
WHERE Country='Germany';This example demonstrates the power of combining INSERT INTO SELECT with conditional logic. Only suppliers located in Germany will be inserted as new customers. The WHERE clause is a crucial part of INSERT INTO SELECT when you need to selectively migrate data.
Key Takeaways
The SQL INSERT INTO SELECT statement is a versatile and efficient way to duplicate or move data within your database. It offers flexibility in choosing which columns and rows to copy, making it suitable for various data management tasks. Whether you’re populating new tables, creating data subsets, or performing data integration, mastering INSERT INTO SELECT is a valuable skill for any SQL developer or database administrator.
