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

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!

You may also be interested in:
  • What to do if you forget your Linux/Mac MySQL password
  • mysql5.7.18 decompressed version to start mysql service
  • Detailed installation and configuration of MySql on Mac
  • How to allow all hosts to access mysql
  • MySQL paging analysis principle and efficiency improvement
  • Detailed steps to connect to MySQL database in pycharm
  • Solve the problem of Access denied for user ''root''@''%'' to database ''xxx'' after creating a database in MySQL

<<:  jQuery implements a simple comment area

>>:  js to achieve simple magnifying glass effects

Recommend

Introduction and analysis of three Binlog formats in MySQL

one. Mysql Binlog format introduction Mysql binlo...

Use of Linux date command

1. Command Introduction The date command is used ...

Share some tips on using JavaScript operators

Table of contents 1. Optional chaining operator [...

Solution to the problem that the docker container cannot be stopped

The solution is as follows: 1. Force delete conta...

A brief discussion on whether MySQL can have a function similar to Oracle's nvl

Use ifnull instead of isnull isnull is used to de...

Summary of data interaction between Docker container and host

Preface When using Docker in a production environ...

Linux uses join -a1 to merge two files

To merge the following two files, merge them toge...

How to call the browser sharing function in Vue

Preface Vue (pronounced /vjuː/, similar to view) ...

Advertising skills in the Baidu Union environment (graphic tutorial)

Recently, students from the User Experience Team o...

jQuery plugin to achieve seamless carousel

Seamless carousel is a very common effect, and it...

React example showing file upload progress

Table of contents React upload file display progr...