In previous article i have given the basic idea about how to create a table in SQL. In this article i would like to give the answer of the most asked question in interview – How to create duplicate table in SQL? I would like to give different examples of How to create duplicate table in SQL?
How to create duplicate table in SQL? – Syntax and Examples
In this section i would like to give multiple ways to create duplicate table in SQL server and oracle. The section includes the ways to create a replica of table with its syntax and real life examples.
SQL Server – Method 1 – How to create duplicate table in SQL? :
You can create a duplicate table in SQL server using following syntax ,
Syntax :
Select top 0 * INTO [dbo].Table_to_Create
From [dbo].Table_name;
You can use above query to create a duplicate table.
Example :
If you want to create duplicate table for Customer table.
Select top 0 * INTO [dbo].Customer_Replica
From [dbo].Customer;
The above query will create the replica of Customer table.
Why this method is not so useful?
It will not create the duplicate table with its constraints. so this method is not so useful.
SQL Server – Method 2 :
The most effective method to create a duplicate table is using SQL Management studio. Following are important steps to create replica of table in SQL Server :
Step 1 : Connect with the database in SQL Management Studio.
Step 2 : Select the specified table (In our example customer table) and right click on it.
Step 3 :Select Script table as –> Create to –> New Query Editor Window. The above process will generate the script. That script is useful to create the table.
Step 4 : Open the new query window and paste the specified script.
Step 5 : This is important step where you need to change the table name,relative keys and constraints as well. (Rename the table as ‘Customer_Replica’
Step 6 : Execute the script.
Oracle- Method 1 – How to create duplicate table in SQL? :
You can also create the duplicate table in oracle.There are two ways of creating duplicate table.
Way 1 : Create duplicate table with data
Way 2 : Create duplicate table without data
Way 1 : Create duplicate table with data
You can create the table using the following syntax,
Create table Backup_Table as select * from Table_To_be_Backup;
Example :
Create table Customer_Replica as Select * from Customer;
The above query will create a table named customer replica with data.
Way 2 : Create duplicate table without data
You can create a duplicate table without data. There are so many situations where we require the skeleton of the table,
Create table Backup_Table as select * from Table_To_be_Backup where 1=2;
Example :
Create table Customer_Replica as Select * from Customer where 1=2;
This will create the replica of the table named customer.
Oracle – Method 2
You can use metadata to create the duplicate table method,
SELECT dbms_metadata.get_ddl( ‘TABLE’, ‘EMPLOYEE’, ‘OTHER_SCHEMA_NAME’ ) FROM DUAL;
The above query will fetch the metadata of Employee table. By using the query you can create replica of table.
Oracle – Method 3
You can create the table or replica of table using Oracle editor. I would like to give you steps in SQL developer editor.
Step 1 : Connect with the database in Oracle Developer and connect to schema.
Step 2 : Select the specified table (In our example customer table) and right click on it.
Step 3 :Select the metadata and check the script from the metadata. The That script is useful to create the table.
Step 4 : Open the new query window and paste the specified script.
Step 5 : This is important step where you need to change the table name,relative keys and constraints as well. (Rename the table as ‘Customer_Replica’
Step 6 : Execute the script.
The above steps are used to create a table with constraints. I hope this article is useful to those who wants to create a duplicate table.If you like this article or if you have any questions kindly comment in comments section.