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
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
To create a clone of employee table named employee_copy
CREATE TABLE employee_copy AS (SELECT * FROM employee);
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
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);
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
To create a table named employee_struct with the same structure as employee table
CREATE TABLE employee_struct AS (SELECT * FROM employee WHERE 0);
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.
Consider you have a table named employee_new as below
CREATE TABLE employee_new
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;