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
 
Share on:



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