The SQL INSERT INTO SELECT Statement
The SQL INSERT INTO SELECT statement is used to insert or copy data into a table from an old table by using a SELECT query in sql. But insert it into the select statement. This operation allows you to copy data that should match the data type in the source table into the target table.
Examples of SQL INSERT INTO SELECT Statement
Example 1: Copying data from the source table to the target table
Let’s say you have two tables, “OldCustomers” and “NewCustomers,” and you want to copy all customer records from the “OldCustomers” table to the “NewCustomers” table. The INSERT INTO SELECT statement can be used, as seen below.
INSERT INTO NewCustomers (CustomerID, CustomerName, Email)
SELECT CustomerID, CustomerName, Email
FROM OldCustomers;
The data from the SELECT query is inserted into the “NewCustomers” table, matching the specified columns.
Example 2: Inserting Data with Transformation
You can also use the INSERT INTO SELECT statement to insert data into a table while performing transformations on the data.
INSERT INTO FormattedOrders (OrderID, FormattedOrderDate)
SELECT OrderID, DATE_FORMAT(OrderDate, ‘%Y-%m-%d’) AS FormattedDate
FROM Orders;
Example 3: Inserting Data with a Filter
To insert a subset of data, you can also use the INSERT INTO SELECT command with a filter condition. For example, if you want to insert only the orders placed in a specific year into another table called “OrdersInYear,” you can do it like this:
INSERT INTO OrdersInYear (OrderID, OrderDate)
SELECT OrderID, OrderDate
FROM Orders
WHERE YEAR(OrderDate) = 2023;
The order ID and order date are among the data that are retrieved by the SELECT query and are to be put.
The data is inserted into the “OrdersInYear” database after being filtered by the WHERE clause to only contain orders placed in 2023.
The INSERT INTO SELECT SQL function is a powerful tool for copying, modifying, or filtering data from one table or query result and inserting it into another command. Within your database, it enables flexible data transfer and manipulation.