Implementation of batch data import in MySQL

How can I import data in batches in MySQL? This is a problem that many people have mentioned. The following describes how to import data in batches in MySQL for your reference.

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.

The following 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 exported tables are the same as those of the target table, and you want to insert all the data, you can use this method: This method is only suitable for exporting the two tables in the same database)

INSERT
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
NewArticles
SELECT * FROM
Articles
;
Type 2: If you only want to import specified fields, you can use this method:

INSERT
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 of the same field type). Otherwise, a Data Conversion error occurs.

1. Copy table data across servers

 
 

  
  
Insert into openrowset ('sqloledb', 'localhost'; 'sa '; '20170101', Test. dbo. Table_ B)
  
  
Select * from Test. dbo. Table_A
  
  
 
  
  
// Enable Ad Hoc Distributed Queries:
  
  
 
  
  
Exec sp_configure 'show advanced options', 1
  
  
Reconfigure
  
  
Exec sp_configure 'ad Hoc Distributed Queries ', 1
  
  
Reconfigure
  
  
 
  
  
// After use, disable Ad Hoc Distributed Queries:
  
  
Exec sp_configure 'ad Hoc Distributed Queries ', 0
  
  
Reconfigure
  
  
Exec sp_configure 'show advanced options', 0
  
  
Reconfigure
  
  
 
 
 

2,

 
 

  
  
// Not cross-Server
  
  
Insert into dbo. Table_ B) select * from dbo. Table_A
 
 

Concatenate the table name and database connection string with code and then execute the SQL statement program function you need.
 

In-depth study on MySQL's data deletion from multiple tables

Simple MySQL large table backup method

Combination of multiple MySQL table result sets

Implementation of MySQL table sharding

MySQL authorization table usage example