How to Efficiently Copy Data Between MySQL Tables?

Copying Data from One Table to Another in MySQL

In MySQL, copying data from one table to another is a common operation often performed for data manipulation or creating a secondary table. Let's consider a scenario where you need to transfer specific fields from an existing table (Table 1) into a new table (Table 2).

Table 1 consists of the following columns:

Column	Description
aid	Auto-incrementing ID
st_id	Student ID
from_uid	Sender's ID
to_gid	Recipient group's ID
to_uid	Receiver's ID
created	Creation timestamp
changed	Modification timestamp
subject	Message subject
message	Message content
link	Message link

Table 2 has a different structure:

Column	Description
st_id	Student ID
uid	User ID
changed	Modifiedtimestamp
status	Current status
assign_status	Assignment status

Copying Data Using MySQL Queries

To transfer data from Table 1 to Table 2, MySQL provides a convenient method using an INSERT INTO query with a SELECT statement. You can achieve this by executing the following query:

INSERT INTO table2 (st_id, uid, changed, status, assign_status)
SELECT st_id, from_uid, now(), 'Pending', 'Assigned'
FROM table1;

Explaining the Query:

• The INSERT INTO statement initializes the insertion operation, specifying the target table (table2).
• The SELECT statement retrieves data from Table 1.
• The column mapping (e.g., st_id TO st_id) ensures that the data is copied to the appropriate columns in Table 2.
• now() is used to set the changed column in Table 2 to the current timestamp.
• The status and assign_status columns are set to 'Pending' and 'Assigned,' respectively.
• The data is transferred from Table 1 to Table 2 by executing the query.

Additional Considerations:

• If you only want to copy a subset of rows from Table 1, you can add a WHERE clause to the SELECT statement.
• To ensure accurate data transfer, make sure that the data types of the columns in both tables are compatible.

The above is the detailed content of How to Efficiently Copy Data Between MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!