How to Insert Data from another Table in SQL?

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.

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

Previous SQL Exercise: Delete using Subquery.
Next SQL Exercise: Update with CASE Statement.