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

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.

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

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!

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.

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://docs.microsoft.com/en-us/sql/relational-databases/tables/duplicate-tables?view=sql-server-2017

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

SELECT * 
INTO target
FROM  source
WHERE 1 = 2

SELECT * INTO table2 FROM table1;

  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.