mysql query data from one table and insert it into another table implementation method

mysql query data from one table and insert it into another table implementation method

Whether in website development or application development, we often encounter situations where we need to import data from a table in MySQL or MS SQL Server into another table in batches, and sometimes we even need to specify the import fields.

This article will take MySQL database as an example to introduce how to import all the data of a table or the data of specified fields into the target table through the SQL command line. This method is also applicable to SQL Server database, that is, T-SQL.

Category 1: If the fields of the two tables (export table and target table) are consistent and you want to insert all the data, you can use this method:

INSERT INTO target table SELECT * FROM source table;

For example, to insert the articles table into the newArticles table, you can use the following SQL statement:

INSERT INTO newArticles SELECT * FROM articles ;

Category 2: If you only want to import specified fields, you can use this method:

INSERT INTO target table (field 1, field 2, ...) SELECT field 1, field 2, ... FROM source table;

Please note that the fields in the above two tables must be consistent, otherwise data conversion errors will occur.

INSERT INTO TPersonnelChange(  
   UserId, 
   DepId, 
   SubDepId, 
   PostionType, 
   AuthorityId, 
   ChangeDateS, 
   InsertDate, 
   UpdateDate, 
   SakuseiSyaId 
 )SELECT 
   UserId, 
   DepId, 
   SubDepId, 
   PostionType, 
   AuthorityId, 
   DATE_FORMAT(EmployDate, '%Y%m%d'), 
   NOW(), 
   NOW(), 
   1 
FROM 
   TUserMst WHERE 
   `Status` = 0 
AND QuitFlg = 0 
AND UserId > 2 

Thank you for reading, I hope it can help you, thank you for your support of this site!