How to copy a database table

You might have come across many scenarios where you need to copy all or part of the contents of a table to another table. This article explains how to clone a table, copy some or all of the data from one table to another or copy just the table structure.

Let's say you have a table named employee created with following SQL.

CREATE TABLE employee
(
empno CHAR(5),
name VARCHAR(15),
dept_name VARCHAR(20),
manager_name VARCHAR(30),
job_title VARCHAR(30)
);

Scenario #1: Create a duplicate copy of a table (Table Cloning)

Syntax:
CREATE TABLE new_table AS (SELECT * FROM old_table);

This will copy both data and table structure from old_table to new_table. In other words new_table will be a clone of old_table

Example:
To create a clone of employee table named employee_copy

CREATE TABLE employee_copy AS (SELECT * FROM employee);

Scenario #2: Create a new table with some or all columns from another table

Syntax:
CREATE TABLE new_table AS (SELECT col1,col2,...colX FROM old_table);

This will create the table new_table which contains some or all columns from old_table

Example:
To create a table named employee_sub with only empno and name column from employee table

CREATE TABLE employee_sub AS (SELECT empno, name FROM employee);

Scenario #3: Create a new table with table structure same as another table without copying the data

Syntax:
CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 0);

new_table will be an empty table with exact same table structure as old_table

Example:
To create a table named employee_struct with the same structure as employee table

CREATE TABLE employee_struct AS (SELECT * FROM employee WHERE 0);

Scenario #4: Copy data between tables having different structures

Syntax:
INSERT INTO destination_table(col1,col2,...colX) SELECT(col8,col9,...colY) FROM source_table

This will copy data from source table columns to the corresponding destination table column. The source and destination column names can be different but they should be of matching data types and of the same length.

Example:
Consider you have a table named employee_new as below

CREATE TABLE employee_new
(
id varchar(5),
fullname varchar(15)
);

To copy empno and name column data from employee table to id and fullname columns in employee_new table

INSERT INTO employee_new(id, fullname) SELECT empno, name FROM employee;

Post a comment

Name

Your Comment

Email (We dont publish it)

Comments

YuriKolovsky | October 27, 2013 8:02 PM |

You seem to forgot to mention if the keys and constraints and triggers are also copied

Joel | October 2, 2016 5:22 PM |

how can i copy data from one column to another