SQL INSERT INTO SELECT

Understanding SQL INSERT INTO SELECT

The SQL INSERT INTO SELECT statement allows you to copy data from one table and insert it into another table. This operation can be performed on entire tables or specific columns, and it supports conditional data transfers, making it a versatile tool for data manipulation in relational databases.

Basic Syntax of INSERT INTO SELECT

To use the INSERT INTO SELECT statement, you need to specify the target table where you want the data to be inserted and the source table from which the data will be copied. The basic syntax is as follows:

Example: Copying Data Between Tables

Consider two tables, employees and archived_employees. Let's copy employees who have left the company from the employees table to the archived_employees table. Here's how you can perform this operation:

Using INSERT INTO SELECT with Conditional Transfers

One of the most powerful features of the INSERT INTO SELECT statement is its ability to include a WHERE clause to filter records. For example, you can selectively copy only those records that meet specific criteria, such as a certain date range or status.

Handling Duplicate Records

When using INSERT INTO SELECT, it's important to consider how duplicate records are handled. By default, the operation will insert all selected records, which may result in duplicates if the target table already contains similar data. To prevent this, you can use conditions or constraints such as UNIQUE index on the target table.

Performance Considerations

While INSERT INTO SELECT is a powerful feature, it can be resource-intensive, especially when working with large datasets. It's important to evaluate the performance impact and consider optimizing your queries, such as by indexing the source table or breaking the operation into smaller batches.