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

Why is the MySQL auto-increment primary key not continuous?

Table of contents 1. Introduction 2. Self-increme...

About the usage and precautions of promise in javascript (recommended)

1. Promise description Promise is a standard buil...

Detailed explanation of using JavaScript WeakMap

A WeakMap object is a collection of key/value pai...

How to create and run a Django project in Ubuntu 16.04 under Python 3

Step 1: Create a Django project Open the terminal...

Web front-end development CSS related team collaboration

The front-end development department is growing, ...

Nodejs converts JSON string into JSON object error solution

How to convert a JSON string into a JSON object? ...

IDEA complete code to connect to MySQL database and perform query operations

1. Write a Mysql link setting page first package ...

Detailed explanation of the JavaScript timer principle

Table of contents 1. setTimeout() timer 2. Stop t...

nuxt.js multiple environment variable configuration

Table of contents 1. Introduction 2. Scenario 3. ...

How to upgrade all Python libraries in Ubuntu 18.04 at once

What is pip pip is a Python package management to...

PHP related paths and modification methods in Ubuntu environment

PHP related paths in Ubuntu environment PHP path ...

Detailed explanation of count without filter conditions in MySQL

count(*) accomplish 1. MyISAM: Stores the total n...

Detailed explanation of jQuery method attributes

Table of contents 1. Introduction to jQuery 2. jQ...

JavaScript implements div mouse drag effect

This article shares the specific code for JavaScr...