How to create duplicate table with new name in SQL Server 2008

Question

How do I create a duplicate table with only the structure duplicated with a new name in SQL server 2008?

I have table with 45 fields so I want to create new with same structure but new name.

Answer 1

Duplication table in the SQL means creating the new table with a same column name from the existing old table which are duplicated. Let us take the table consists of more than 50 fields and you want to create a table with exactly same column but in another new table. If we start creating table from the scratch, it would take more time and to avoid this table duplication can be done which creates a new table with same fields. For example let us table this sample table, Tbldepartment which contains following fields,

If we want to duplicate the table,

SELECT *  
INTO [NewTbl_Apartments]  
FROM [Tbl_Apartments]  
WHERE 1 = 2  

Here where is mentioned as 1=2, this will prevent data coping from the existing table into newly created duplicate table.

Answer 2

To create a new table from an existing table: (copying all rows from Old_Table into de New_Table):

SELECT * INTO New_table FROM  Old_Table

To copy the data from one table to another (when you have already created them):

Insert into Table_Name2 select top 1 * from Table_Name1

Remember to remove the top 1 argument and apply a relevant where clause if needed on the Select

Answer 3

  SELECT * INTO newtable FROM oldtable where 1=2

Where 1=2 is used when you need to copy the complete structure of a table without copying the data.

 SELECT * INTO newtable FROM oldtable 

To create a table with data you can use this statement.

Answer 4

In SSMS right click on a desired table > script as > create to > new query
-change the name of the table (ex. table2)
-change the PK key for the table (ex. PK_table2)

USE [NAMEDB]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table_2](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_table_2] PRIMARY KEY CLUSTERED 
(
[reference] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = 
OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 5

SELECT * INTO table2 FROM table1;

Answer 6

I have used this query it is created new table with existing data.

Query : select * into [newtablename] from [existingtable]

Here is the link Microsoft instructions. https://learn.microsoft.com/en-us/sql/relational-databases/tables/duplicate-tables?view=sql-server-2017

Answer 7

Here, I will show you 2 different implementation:

First:

If you just need to create a duplicate table then just run the command:

SELECT top 0 * INTO [dbo].[DuplicateTable]
FROM [dbo].[MainTable]

Of course, it doesn't work completely. constraints don't get copied, nor do primary keys, or default values. The command only creates a new table with the same column structure and if you want to insert data into the new table.

Second (recommended):

But If you want to duplicate the table with all its constraints & keys follows this below steps:

1. Open the database in SQL Management Studio.
2. Right-click on the table that you want to duplicate.
3. Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
4. Change the table name and relative keys & constraints in the script.
5. Execute the script.

Answer 8

My SQL Server Management Studio keeps asking me how I can make it better, I have an idea! The ability to highlight a table and then, ctrl C, ctrl V! would be great and the answer to this question at the same time!

Answer 9

SELECT * 
INTO target
FROM  source
WHERE 1 = 2

Answer 10

Right click on the table in SQL Management Studio.

Select Script... Create to... New Query Window.

This will generate a script to recreate the table in a new query window.

Change the name of the table in the script to whatever you want the new table to be named.

Execute the script.