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

This article mainly introduces the relevant information about the method of mysql querying data from one table and inserting it into another table. Friends who need it can refer to the following

mysql Querying data from one table and inserting it into another table Implementation method of inserting into another table

Whether it is in website development or application development, we often encounter the need to batch import data from a certain table of MySQL or MS SQLServer into another table situations, it is even sometimes necessary to specify import fields.

This article will take the MySQL database as an example to introduce how to import all data of a table or data of specified fields into the target table through the SQL command line. This method is also applicable to SQLServer 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 pay attention to the above two tables The fields 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

The above is the detailed content of mysql query data from one table and insert into another table implementation method. For more information, please follow other related articles on the PHP Chinese website!