MySQL details the insert into syntax for table replication

  • 2020-05-27 07:21:15
  • OfStack

In the development of web, we often need to insert data from one table into another table, and sometimes we need to specify import fields, and only need to import records that do not exist in the target table. Although these can be separated into simple sql in the program to achieve, using 1 sql will save a lot of code. Below, I take mysql database as an example to explain in case 11:
Two tables: insertTest and insertTest2, with test data in the former

create table insertTest(id int(4),name varchar(12));
insert into insertTest values(100, ' liudehua');
insert into insertTest values(101, ' zhourunfa');
insert into insertTest values(102, ' zhouhuajian');

1. If field 1 of two tables is identical and you want to insert all the data, you can use this method:
INSERT INTO target table SELECT * FROM source table;

insert into insertTest select * from insertTest2; 

2. If you only want to import the specified fields, you can use this method:

INSERT INTO  The target table  ( field 1,  field 2,  ... ) SELECT  field 1,  field 2,  ...  FROM  The source table ;  

Note that the order of the fields must be 1 to 1.

insert into insertTest2(id) select id from insertTest2; 

3. If you need to import only records that do not exist in the target table, you can use this method:

 INSERT INTO  The target table   
 ( field 1,  field 2,  ... )  
 SELECT  field 1,  field 2,  ...   
 FROM  The source table   
 WHERE not exists (select * from  The target table   
 where  The target table . Compare the field  =  The source table . Compare the field );

1 > . Insert multiple records:

insert into insertTest2
(id,name)
select id,name
from insertTest
where not exists (select * from insertTest2
where insertTest2.id=insertTest.id);

2 > Insert 1 record:

insert into insertTest
(id, name)
SELECT 100,  ' liudehua'
FROM dual
WHERE not exists (select * from insertTest
where insertTest.id = 100);

Use dual as the table name, and select statements are followed directly by the values of the fields to insert.

Related articles: