Copying / exporting data to an existing table. INSERT....SELECT
Copying / exporting data to an existing table. INSERT....SELECT
We can export or copy data from one table to another table by using insert command. We can also use replace statement to copy data. We will try with insert command first. The difference between insert and replace statement is the way primary key or the unique key is handled. Here we are using two existing tables and inserting data from one table to other. To learn how to copy data by creating a new table you can read create table command here.
We will use our student table for this. You can download the sql dump file of two tables at the end of this page for your use.
Student table with 35 records Student2 table with 0 records
Now we will apply this sql command to export data from student table to student2 table
insert into student2 select * from student;
With this command we will copy all data from student to student2 table We can restrict the data we want to export by adding where clause to the query like this.
insert into student2 select * from student where class = 'Four';
Please note that if the student2 table is not empty then the unique constraints for the field id will not allow duplicate id so data will not be updated. Here if we want to update student2 table then we have to use replace command in place of insert command.
replace into student2 select * from student where class = 'Four';
WE can use selected columns to insert the records, here is an example.
insert into student2 (id,name,class,mark,sex) select id,name,class,mark,sex from student
on Duplicate Key Update
Without using replace command we can also use on duplicate key update to update the student2 table.
After keeping all the records in student2 tables let us try this query
insert into student2 (id,name,class,mark,sex) select id,name,class,mark,sex from student where student.id=3
This query will generate the error like this as id =3 is already exist in student2 table and that will violate the unique constraint.
#1062 - Duplicate entry '3' for key 'PRIMARY'
Now let us try this query
insert into student2 (id,name,class,mark,sex) select id,name,class,mark,sex from student where id=3 on duplicate key update mark=5
The above query will insert the record and update the mark column to 5, this way we can update records using on duplicate key command.
