The SQL INSERT INTO SELECT statement allows you to insert data from one table into another. This can be very useful when you want to copy data from one table to another or when you want to consolidate data from multiple tables into a single table. The basic syntax of the INSERT INTO SELECT statement is as follows:
sqlINSERTINTO targettable (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM sourcetable WHERE conditions;
Here's a breakdown of the syntax components:
target_table: The name of the table where you want to insert the data.
column1, column2, column3, ...: The specific columns in the target table that you want to insert data into. If you want to insert data into all columns, you can omit this part.
source_table: The name of the table from which you want to select data to insert.
conditions: Optional conditions to filter the data being selected from the source table. This part is optional.
Example:
Let's say you have a source table called orders with columns order_id, customer_id, and order_date, and you want to copy data from this table into a target table called archive_orders with the same columns:
sqlINSERTINTO archiveorders (orderid, customerid, orderdate) SELECT orderid, customerid, orderdate FROM orders WHERE orderdate <'2023-01-01';
In this example, the query copies all orders made before January 1, 2023, from the orders table into the archive_orders table.
Remember that the column data types and order must match between the source and target tables. Also, be cautious when using the INSERT INTO SELECT statement, as it can potentially insert a large amount of data and affect the performance of your database. Always make sure to test your queries on a small dataset before applying them to larger datasets.