Backup Table in SQL

by Shikha Katariya · August 18, 2015

Backup Table in SQL, as the name suggests, is the copy of an existing table whose backup we need to take .

Constraints on the original table are not copied in the Backup table except Not Null Constraint.

Indexes on the original table are not copied in the Backup table.

Backup Table containing all the columns from the original table –

CREATE TABLE <Backup TableName> AS SELECT * FROM <Existing Tablename>;

CREATE TABLE <Backup TableName> AS SELECT <Col1 >, <Col2>…<ColN> FROM <Existing Tablename>;

Backup Table containing selected columns from the original table based on certain conditions –

CREATE TABLE <Backup TableName> AS SELECT * FROM <Existing Tablename> WHERE <Condition> ;

Backup Table containing selected columns from the original table based on certain conditions –

CREATE TABLE <Backup TableName> AS SELECT <Col1> , <Col2>…<ColN>

FROM <Existing Tablename> WHERE <Condition> ;

To Copy only Structure ( all the columns) and not the data of the Original table in Backup Table –

CREATE TABLE <Backup TableName> AS SELECT * FROM <Existing Tablename> WHERE 0=1 ;

To Copy only Structure ( selected columns) and not the data of the Original table in Backup Table –

CREATE TABLE <Backup TableName> AS SELECT <Col1> , <Col2>….<ColN> FROM <Existing Tablename> WHERE 0=1 ;

Note : Instead of the condition “0=1”, any other condition equating mismatch numbers can be given like 3=4, 6=8 etc.

INSERT INTO <Tablename> SELECT * FROM <Backup Tablename> ;

INSERT INTO <Tablename> (<col1> , <col2>…<colN>) SELECT (<col1> , <col2>…<colN>)

FROM <Backup Tablename> ;

Note : The number and datatypes of columns in both backup table and destination table should be same.

INSERT INTO <Tablename> (<col1> , <col2>…<colN>) SELECT * FROM <Backup Tablename> ;

Note : In case the backup table contains selective columns , then while copying data in the destination same columns should be specified.