SQL INSERT INTO SELECT Statement

  • 696 Views
  • Last Post 08 August 2023
lucy posted this 11 May 2020

This post shows you how to use SQL INSERT INTO SELECT Statement to another table.

INSERT INTO table2 (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM table1

WHERE condition;

For example

INSERT INTO Customers (CustomerName, City, Country)

SELECT Name, City, Country FROM Persons;

AnnyJones09 posted this 08 August 2023

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:

sql INSERT INTO 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:

sql INSERT INTO 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.

Please visit:- https://www.janbasktraining.com/online-sql-server-training

Close