How to INSERT data from one Table into another in MySQL

If You want to insert one table selected data from another table. there are some syntax for insert data. Using an INSERT…SELECT statement, we can insert multiple rows of data into a table, with the result of a SELECT statement which can get data from one or more tables.

In MySQL, copying data from one table to another is a common task that comes in handy during data migration, creating backups, or moving records between tables for better organization. Whether you want to copy specific rows or entire datasets, MySQL offers a flexible INSERT INTO ... SELECT query to accomplish this. In this guide, we’ll explore the process of inserting data from one table into another in MySQL using PHP, complete with practical examples.

Why Use INSERT INTO ... SELECT?

The INSERT INTO ... SELECT syntax allows you to copy rows from one table to another seamlessly. Some of the reasons to use this method include:

• Data Migration: Move data between tables for archiving or reorganizing.
• Backups: Create backups by copying data from one table to another for safety.
• Data Transformation: Transfer data between normalized tables or apply modifications before storing it in the new table.

Use the following MYSQL query to insert data from one table to another in MySQL.

INSERT INTO insert_table_name(A, B, C)
  SELECT A, B, C
    FROM select_table_name

Here are Syntax for insert one table to another table like insert_table_name (target_table) and select_table_name (source_table)

INSERT INTO 
    employee_master (emp_name, city, state, zip_code) 
SELECT
    emp_name,
    city,
    state,
    zip_code
FROM
    employee_details
WHERE
    city IN ('Lucknow', 'Kanpur')

Performance Considerations

While the INSERT INTO ... SELECT statement is a powerful tool, it’s essential to be mindful of performance, especially when dealing with large datasets. Here are a few tips to keep in mind:

• Indexes: Ensure both the source and target tables are indexed appropriately for the columns involved in the WHERE clause.
• Batch Processing: For large datasets, consider copying the data in batches to avoid overloading the server.
• Transactions: Use transactions for critical operations to ensure data integrity in case of failure.