Creating Duplicate Table With New Name From Existing Table in SQL Server 2012

In this article, you will see how to create a duplicate table with a new name using a script in SQL Server.

In this article, you will see how to create a duplicate table with a new name using a script in SQL Server. Suppose you have a table with 40 fields and you want to create a table with the same structure but a new name. If you create a new empty table then it will take extra time to define all the fields again. So instead we create a script of the table and replace the table name with the new name from the table script which creates a new table with the same column names, data types, and nullable settings. So let's take a look at a practical example of how to create a script in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 
 
Creating the Table in SQL Server
  1. CREATE TABLE [dbo].[UserDetail]  
  2. (  
  3.             [User_Id] [intNOT NULL,  
  4.             [FirstName] [varchar](255) NULL,  
  5.             [LastName] [varchar](255) NOT NULL,  
  6.             [Address] [varchar](255) NULL  
  7. )  
Now insert data into the table. The table will look as in the following:
 
Table-in-SQL-Server.jpg
 
Creating Table Script in SQL Server Management Studio
 
Now Press F8 to open the Object Browser in SQL Server Management Studio and expand it.
 
Database -> Table then right-click then select Script Table as -> Create to -> New Query Editor Window.
 
Create-table-Script-in-SQL-Server.jpg
  1. USE [master]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. SET ANSI_PADDING ON  
  8. GO  
  9. CREATE TABLE [dbo].[UserDetail](  
  10.             [User_Id] [intNOT NULL,  
  11.             [FirstName] [varchar](255) NULL,  
  12.             [LastName] [varchar](255) NOT NULL,  
  13.             [Address] [varchar](255) NULL  
  14. ON [PRIMARY]  
  15. GO  
  16. SET ANSI_PADDING OFF  
  17. GO  
The above script generates a script for the UserDetail table in a new query window.
 
Now change the name of the table in the script to whatever you want the new table to be named.
  1. USE [master]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. SET ANSI_PADDING ON  
  8. GO  
  9. CREATE TABLE [dbo].[UserDetailRecreate] -- Changing the name of the above table  
  10. (  
  11.             [User_Id] [intNOT NULL,  
  12.             [FirstName] [varchar](255) NULL,  
  13.             [LastName] [varchar](255) NOT NULL,  
  14.             [Address] [varchar](255) NULL  
  15. ON [PRIMARY]  
  16. GO  
  17. SET ANSI_PADDING OFF  
  18. GO  
Now Execute the script. Press F5
 
change-table-name-from-Script-in-SQL-Server.jpg 
 
Now refresh the Master database and expand it to see the new table in the Object Explorer. 
 
Object-Explorer-in-SQL-Server.jpg 
 
Create a Duplicate Table with New Name from Existing table programmatically
  1. SELECT *  
  2. INTO [UserDetailRecreate]  
  3. FROM [UserDetail]  
  4. WHERE 1 = 2  
Here, 1=2 will prevent the data from being copied from UserDetailto the UserDetailRecreate table.