MySQL detailed introduction to the insert into syntax for table replication

In web development, we often need to insert data from a table to another table. Sometimes, we also need to specify the import field to import records that do not exist in the target table, although these can be split into simple SQL statements in the program, using one SQL statement will save a lot of code. The following uses the mysql database as an example to describe the situation one by one:
Two tables: insertTest and insertTest2. The former has test data.
Copy codeThe Code is as follows:
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 the fields of the two tables are consistent and you want to insert all the data, you can use this method:
Insert into target table SELECT * FROM source table;
Copy codeThe Code is as follows:
Insert into insertTest select * from insertTest2;

2. If you only want to import specified fields, you can use this method:
Copy codeThe Code is as follows:
Insert into target table (Field 1, Field 2 ,...) SELECT Field 1, Field 2 ,... FROM source table;

Note that the order of fields must be consistent.
Copy codeThe Code is as follows:
Insert into insertTest2 (id) select id from insertTest2;

3. If you only need to import records that do not exist in the target table, you can use this method:
Copy codeThe Code is as follows:
Insert into target table
(Field 1, Field 2 ,...)
SELECT Field 1, Field 2 ,...
FROM source table
WHERE not exists (select * from target table
Where target table. Comparison field = source table. Comparison field );

1>. Insert multiple records:
Copy codeThe Code is as follows:
Insert into insertTest2
(Id, name)
Select id, name
From insertTest
Where not exists (select * from insertTest2
Where insertTest2.id = insertTest. id );

2> insert a record:
Copy codeThe Code is as follows:
Insert into insertTest
(Id, name)
SELECT 100, 'liudehua'
FROM dual
WHERE not exists (select * from insertTest
Where insertTest. id = 100 );

When dual is used as the table name, the values of the fields to be inserted are directly followed by the select statement.