SQL Insert Into Select – A Comprehensive Guide on SQL Insert Into Select Statement

Deep dive into a detailed discussion about the SQL Insert Into Select statement.

SQL, an abbreviation for Structured Query Language, is a powerful tool utilized by developers to communicate with and manipulate databases. Among the various statements provided by SQL, Insert Into Select is a versatile feature that can be utilized to copy data from one table and insert it into another table.

Introduction

In a database, data management is key to preserving the quality, consistency, and integrity of data. Sometimes, you need to transfer specific records from one table to another. For instance, archiving old data, backing up current data, or relocating certain data to a different table for analytical purposes. The Insert Into Select statement simplifies this process by combining two key SQL operations – Insert Into and Select.

Syntax

The basic syntax of an SQL Insert Into Select statement is as follows:

INSERT INTO table2 (column1, column2, column3,...)
SELECT column1, column2, column3,...
FROM table1
WHERE condition;

Here:

• table2 is the table where the data will be inserted.
• column1, column2, column3,... are the columns in table2 where the selected data will be inserted. These columns should match the number and type of columns selected from table1.
• table1 is the source table from where the data is being selected.
• The WHERE clause is optional and specifies the condition that must be fulfilled for the records to be selected and inserted.

A Practical Example

Consider you have two tables: Orders and Archived_Orders. The Orders table contains all active orders, while the Archived_Orders table is where you want to move orders that are completed.

The Orders table looks like this:

| OrderID | Customer | Product | Status   |
|---------|----------|---------|----------|
| 1       | John     | Apples  | Active   |
| 2       | Sarah    | Bananas | Active   |
| 3       | Maria    | Oranges | Completed|

And the Archived_Orders table is currently empty:

| OrderID | Customer | Product | Status   |
|---------|----------|---------|----------|

You want to insert all Completed orders from the Orders table into the Archived_Orders table. You can do this using the Insert Into Select statement like so:

INSERT INTO Archived_Orders (OrderID, Customer, Product, Status)
SELECT OrderID, Customer, Product, Status
FROM Orders
WHERE Status = 'Completed';

After running this query, the Archived_Orders table will look like this:

| OrderID | Customer | Product | Status   |
|---------|----------|---------|----------|
| 3       | Maria    | Oranges | Completed|

And the Orders table will remain unchanged.

Conclusion

The SQL Insert Into Select statement is an essential tool for data management. It allows developers to select and insert data into tables efficiently, based on specified conditions.

Remember that SQL can be different across different database systems. The information in this blog applies to most SQL systems like MySQL, SQL Server, and PostgreSQL, but may have slight differences in Oracle or other databases. Always refer to the documentation for your specific system when unsure.