Backup Table in SQL

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.

Syntax for Creating 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.

Syntax to Load entire Data into Original Table from Backup Table :

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

Syntax to Load entire Data into Original Table from Backup Table :

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.

Print Friendly, PDF & Email
Order By Clause in SQL
Distinct Clause in SQL
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *