SQL Temporary Tables And Clone Tables

When dealing with large amounts of data, we need to take care that the integrity and correctness of data are always taken care of. We use temporary tables to make short-lived tables, which automatically vanish once the session in which they are made terminates.

Clone tables are copies of already existing tables that have all the properties of the table. We need to follow various naming conventions and rules while cloning or making a temporary table.

In this tutorial, we will dive into the steps and processes we need to follow to clone a table and also how to make a temporary table.

What are Temporary Tables in SQL?

Temporary tables are made when we need to access a set of given data again and again. Temporary tables automatically vanish once the session in which the temporary table is made terminates.

We can delete the temporary tables manually too, by executing the delete query on the temporary table. The temporary tables we make are put in the tempdb, which is a system database.

The name of the temporary table needs a hash sign (#) prefix in the earlier versions.

We have two types of temporary tables:

1. SQL Local Temporary Table

Local tables vanish automatically once the connection in which they are made terminates.

To create local temporary tables, we need to prefix the table name with a hash sign ‘#’.

But in the newer version, we don’t require the hash sign.

Syntax: 

 CREATE TABLE tableName(
columnName1 varchar(50),
columnName2  int,
…..);

2. SQL Global Temporary Table

Global tables are made by prefixing the table name with two hash signs i.e. ‘##’. But in the newer version, we don’t need to use the hash sign.

Syntax: 

CREATE TABLE ##tableName(
columnName1 varchar(50),
columnName2  int,
…..);

Steps to Create a Temporary Table in SQL

1. Create SQL Temporary Table:
Syntax: 

CREATE TEMPORARY TABLE tableName(
columnName1 varchar(50),
columnName2  int,
…..);

Example:
Query:

CREATE TEMPORARY TABLE DataFlair(
Name varchar(50),
Age int,
Empid varchar(10)
);

Output:

2. Insert values into the Temporary table :
Syntax:

INSERT INTO tableName Values(val1,val2,val3,....);

Example:
Query:

INSERT INTO  DataFlair VALUES('Siya',24,'A02'),
('Dharm',26,'B23'),
('Raghav',28,'D45'),
('Naman',31,'D12'),
('Richa',22,'A01');
select * from dataflair;

Output:

3. Select values from Temporary table:
Syntax:

SELECT * FROM tableName;

Example:
Query:

SELECT  * FROM DataFlair ORDER BY empid;

Output:

4. Create a Temporary Table from an existing table:
Syntax:

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table ;

Example:
Query:

CREATE TEMPORARY TABLE DataFlair_temp1
SELECT * FROM DataFlair ;
SELECT * FROM dataflair_temp1;

Output:

Drop a Temporary Table in SQL

Syntax:

DROP TABLE tempTableName;

Example:
Query:

DROP TABLE DataFlair_temp1;

Output:

What are SQL Clone Tables?

We use clone tables when we require the table to be an exact copy in terms of index, constraints and default values, and so forth.

We use clone tables to create exact copies of a table, this is beneficial when our regular Create Select statements can’t yield accurate results.

Steps to Clone a Table in SQL

1. Use SHOW CREATE TABLE to get all the details of the table whose clone we need to make.
Syntax: 

SHOW CREATE TABLE tableName;

Example:
Query:

Show create table DataFlair;

Output:

2. Modify the resultant SELECT query to the name of the clone table to get the same clone table.
Syntax: 

CREATE TABLE cloneTableName(
colName1 datatype,
colName2 datatype,
….);

Example:
Query:

CREATE TEMPORARY TABLE dataflair_clone (  `Name` varchar(50) DEFAULT NULL,  `Age` int(11) DEFAULT NULL,
`Empid` varchar(10) DEFAULT NULL);
SELECT * FROM DataFlair_clone;

Output:

3. If we need the data as well then we can use the INSERT INTO statement to get the data from the original table.
Syntax:

INSERT INTO tableNameClone (col1,col2,....) 
SELECT  col1,col2,... FROM tableName;

Example:
Query:

INSERT INTO DataFlair_clone (Name,Age,Empid) SELECT  Name , Age, Empid FROM DataFlair;
SELECT * FROM DataFlair_clone;

Output:

Drop a Clone Table in SQL

Syntax: 

DROP TABLE tableName;

Example:
Query: 

DROP TABLE DataFlair_clone;

Output:

Difference Between SQL Temporary Tables and Clone Tables

Differences between Temporary and Clone tables are as follows:

1. Temporary tables are made in the tempdb, whereas clone tables are made in the memory.
2. Temporary tables vanish once the connection in which they are made vanishes while clone tables remain in memory until they are manually dropped.
3. SQL Temporary tables are a subset of the original table, whereas clone tables are the exact copy of the original table.
4. Temporary tables allow index and constraint modification while this is not permissible in clone tables.
5. Temporary tables can’t be put in transactions, whereas clone tables can be put in transactions.
6. SQL Temporary tables fasten the queries while clone tables create no such difference.

Summary

In this article, we have understood in detail about the clone table and temporary table in SQL. Temporary tables are handy when we have a large amount of data, but we need only a small part of it for use.

Whereas clone tables come in handy when we require the same table to perform sensitive transactions and chances of failures are there.

The use of temporary and clone tables ensures that our data remains safe in all conditions whatsoever.