Copy Data from one Table to another in SQL

This article shows how to copy data from one table to another using SQL. It covers the following scenarios:

  • copy between two tables having the exact same structure.
  • copy to a table having an ID automatically incremented.
  • copy between two tables which do not have the same structure.

Simple copy.

We want to copy rows from a table called products to a table called products_backup. These two tables have the same structure. They both have three columns:

  • ID (auto increment in the products table)
  • product name.
  • product price.

We assume that in the products_backup table, the ID is not automatically incremented.

The syntax is the following:

insert into
products_backup
select * from products

If the column ID was an auto increment, the syntax would be the following:

insert into products_backup
(product_name, product_price)
select
product_name,
product_price
from products

Copy and at the same time modify data.

We want to copy data from the table products to the table products_config.
The table products_config has the following columns:

  • ID (auto increment).
  • product_name: we should take the value of this column from the column product_name in products.
  • price: we should take the value of this column from the column product_name in products and multiply it by 1.3.
  • status: this field is not in the products table. It should have the value “new” in each copied row.

The syntax is the following:

insert into products_config
(product_name,price,status)
select
product_name,
product_price*1.3,
'new'
from products
 
Please help this site survive, share on:



Author:
Last udpated: September 20, 2015
Created: October 14, 2014

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

2 questions on “Copy Data from one Table to another in SQL

  1. Great website. Thanks.
    The second box of syntax has the wrong table name. It has ‘products’ when it should have ‘producks_backup’

    insert into products?????????
    (product_name, product_price)