CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Alex
Alex

Reputation: 1733

Duplicating a TABLE using Microsoft SQL Server Management

Need to duplicate a TABLE using Microsoft SQL Management Studio 2008

The TABLE needs to duplicate all table row (Primary Key) ID as well.

Upvotes: 65

Views: 144884

Answers (4)

SQLMenace
SQLMenace

Reputation: 135181

In SSMS open a new query window and then do something like this:

SELECT * INTO NewTable
FROM OldTable

Change NewTable to the name that the new table should have. Change OldTable to the name of the current table.

This will copy over the basic table structure and all the data. It will NOT duplicate any of the table constraints; you will need to script those out and change the names in those scripts.

Upvotes: 136

Dänu
Dänu

Reputation: 5949

One way to copy the table structure (including default values) but NOT the actual table values is the copy / paste solution that is documented here. It works for Management Studio 2005 upwards. You just have to select all columns in the design then Edit -> Copy. Create a new table and the Edit -> Paste.

Upvotes: 3

Dane Stevens
Dane Stevens

Reputation: 545

An easy way to copy a table and all of it's data:

SELECT * INTO 
    [DATABASE_NAME].[SCHEMA_NAME].[NEW_TABLE_NAME] 
FROM 
    [DATABASE_NAME].[SCHEMA_NAME].[OLD_TABLE_NAME]

The SCHEMA_NAME is often dbo

Upvotes: 22

dshefman
dshefman

Reputation: 1017

To duplicate a table and the data rows in the table, right-click on the database that contains the table you want to duplicate, then click 'Tasks' then 'Import Data...". See the screenshot below for visual representation. Then, follow the instructions in the "SQL Server Import and Export Wizard." Select the table to be duplicated as the 'source' and write in a made-up table name of your choice for the 'destination'. When finished on the last screen (see screenshot below), click 'Next', then 'Finish' and the Wizard will show you the progress of the data transfer until complete.

enter image description here

enter image description here

Upvotes: 12

Related Questions