In web development, we often need to insert data from one table into another, and sometimes we need to specify an import field, which only needs to import records that do not exist in the target table, although these can be broken down into simple SQL in the program, but with one SQL it saves a lot of code. Here I take the MySQL database as an example of the situation explained: 1. If the fields of 2 tables are consistent 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 want to import only the specified fields, you can use this method: INSERT into Target table (field 1, Field 2, ...) SELECT Field 1, Field 2, ... from source table; (the field must be consistent here) 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 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: INSERT INTO InsertTest2 (id,name) Select Id,namefrom inserttestwhere NOT EXISTS (SELECT * FROM Inserttest2where inserttest2.id=inserttest.id); 2> Insert a record: INSERT INTO inserttest (ID, name) SELECT, ' Liudehua ' from dual where NOT exists ( SELECT * from Inserttest where inserttest.id = 100);
Implementation code in MySQL that inserts data from one table into another table