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;
