Mysql queries data from one table and inserts it into another table.

Mysql queries data from one table and inserts it into another table.

How does mysql query data from one table and insert data into another table?

Whether in website development or application development, we often encounter the need to batch import data from a MySQL or MS SQLServer table to another table, you even need to specify the import field.

This document uses the MySQL database as an example to describe how to import all the data of a table or the data of a specified field to the target table through the SQL command line. This method also applies to SQLServer databases, that is, T-SQL.

Category 1,If the fields of the two tables (the export table and the 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 a specified field, you can use this method:

Insert into target table (Field 1, Field 2,...) SELECT Field 1, Field 2,... FROM source table;

Note that the fields in the preceding two tables must be consistent; otherwise, a Data Conversion error occurs.

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 this article. I hope it will help you. Thank you for your support for this site!