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.

Copy data to an existing table by INSERT or REPLACE with constraints using on DUPLICATE KEY UPDATE


Here we are using two existing tables and inserting data from one table to other.
copy data by creating a new table, read create table command here .

The difference between INSERT and REPLACE statement is the way primary key or the unique key is handled.
We will use our student table for this. You can download the sql dump file of two tables at the end of this page.
Student table with 35 records
Student2 table with 0 records

INSERT ... SELECT

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 (filter ) 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)
 SELECT id,name,class,mark FROM student
In above query we have not included gender column and this column accepts NULL as default value ( or it has some other default value ).

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,gender)
 SELECT  id,name,class,mark,gender 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,gender) 
 SELECT id,name,class,mark,gender
  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.

Updating more than one column

INSERT INTO student2(id,name,class,mark,gender) 
SELECT id,name,class,mark,gender FROM student WHERE class='Four' 
ON DUPLICATE KEY UPDATE mark=200,gender=student.gender;

Transfer data from one table to other

In both tables columns are not matching but one column only is same. Here p_id is used from products table and used in stock table.
INSERT INTO plus2_inv_stock (p_id,qty,price_sell) SELECT p_id ,0,0 FROM `plus2_inv_products`


Down load the SQL DUMP of this student table
SQL References Copy Table Updating multiple records by ON DUPLICATE KEY UPDATE

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    nikhil

    20-05-2009

    plz tell me about, database? what is the procedure to copy one database to annother database in mysql.
    shekhar sinha

    06-08-2009

    can constraints be copied from one table to another table?
    shekhar sinha

    06-08-2009

    can only primary key data be deleted or dropped?
    shekhar sinha

    06-08-2009

    can we define more than one primary key in one table?
    prasath

    18-01-2010

    select * into "new table name" from database.dbo.tablename
    eliazar espina

    17-07-2010

    can you help me with copying data from table1 to table2 for example in postgres database..
    nikita

    18-08-2010

    can we copy content of a table to another table using ||
    swapna.k

    07-10-2010

    Could any one tell me the answer how to Create one table from another table without copying the data from the first table.
    Sromana Mukhopadhyay

    16-03-2018

    I really like the REPLACE SELECT statement.

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer