SQL INSERT INTO SELECT Statement
The SQL INSERT INTO SELECT statement is used to insert data into a table from the result set of a SELECT statement. It allows us to insert data from one table into another table, or even from multiple tables into a single table.
Orders Table:
| OrderID | CustomerID |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
Customers Table:
| CustomerID | CustomerName |
|---|---|
| 10 | William |
| 20 | Jane |
| 30 | Michael |
To insert data from the Customers table into the Orders table, we can use the following INSERT INTO SELECT statement:
1INSERT INTO Orders (CustomerID, CustomerName) 2SELECT CustomerID, CustomerName 3FROM Customers;
The output of the above query will be: Orders Table:
| OrderID | CustomerID | CustomerName |
|---|---|---|
| 1 | 10 | William |
| 2 | 20 | Jane |
| 3 | 30 | Michael |
As we can see from the output, the SQL INSERT INTO SELECT statement inserts data from the Customers table into the Orders table. The INSERT INTO statement specifies the target table Orders and the columns that will be inserted into the target table (CustomerID, CustomerName). The SELECT statement specifies the source table Customers and the columns that will be inserted into the target table (CustomerID, CustomerName).
It's important to note that the columns in the target table must match the number and data types of the columns in the SELECT statement. If the columns in the target table do not match the columns in the SELECT statement, an error will be raised.