How to Insert Data from another Table in SQL?

Last update on September 20 2025 13:31:09 (UTC/GMT +8 hours)

Insert from another Table

Write a SQL query to copy data from one table into another table.

Solution:

-- Insert employees from the "OldEmployees" table into the "Employees" table.
INSERT INTO Employees (EmployeeID, Name, Age, Salary) -- Specify the target columns.
SELECT EmployeeID, Name, Age, Salary FROM OldEmployees; -- Copy data from another table.

Explanation:

Purpose of the Query :

The goal is to copy data from the OldEmployees table into the Employees table.
This demonstrates how to use the INSERT INTO ... SELECT statement to transfer data between tables.

Key Components :

INSERT INTO Employees (EmployeeID, Name, Age, Salary) : Specifies the target table (Employees) and the columns where data will be inserted.
SELECT EmployeeID, Name, Age, Salary FROM OldEmployees : Retrieves data from the OldEmployees table to be inserted into the Employees table.

Why Use INSERT INTO ... SELECT? :

The INSERT INTO ... SELECT statement is useful for transferring or duplicating data between tables, especially when migrating or consolidating data.
For example, if you are transitioning from an old system (OldEmployees) to a new system (Employees), this query allows you to copy relevant data seamlessly.

Real-World Application :

For example, in a company database, you might use this query to migrate employee records from a legacy table (OldEmployees) to a new table (Employees) as part of a system upgrade or restructuring process.

Additional Notes:

INSERT INTO ... SELECT is a common technique for moving data between tables during system upgrades or consolidations.
Scenarios where copying data between tables is appropriate, such as:

Migrating data from legacy systems to new systems.
Archiving old data into a separate table while retaining it in the original table.
Combining data from multiple tables into a single consolidated table.

Important Considerations :

Ensure that the columns in the SELECT statement match the columns specified in the INSERT INTO clause in both number and data type.
Verify that the source table (OldEmployees) contains valid data before performing the operation.
Be cautious about inserting duplicate records if the target table already contains similar data.

For more Practice: Solve these Related Problems:

Write a SQL query to copy all active employees from the `employees` table into the `active_employees` table.
Write a SQL query to copy all products with a price greater than 100 from the `products` table into the `premium_products` table.
Write a SQL query to copy all customers from the `customers` table who have placed orders into the `loyal_customers` table.
Write a SQL query to copy all orders from the `orders` table placed in the last month into the `recent_orders` table.

Go to:

PREV : Delete using Subquery.
NEXT : Update with CASE Statement.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.