Duplicating a TABLE using Microsoft SQL Server Management
Solution 1
In SSMS open a new query window and then do something like
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 do any of the constraints, you need to script those out and change the names in those scripts
Solution 2
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
Solution 3
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.
Solution 4
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.
Related videos on Youtube
Comments
-
Alex about 2 years
Need to duplicate a TABLE using
Microsoft SQL Management Studio 2008
The TABLE needs to duplicate all table row (Primary Key) ID as well.
-
Joe Bigler over 8 yearsI tried this and it didn't work at first because I created the query by clicking databases. This creates the query in the master database, which didn't work. I got an error saying the object 'MyTable' didn't exist. When I created the query in the database that contained the table, it worked fine. Just thought I'd mention it.
-
biko almost 8 yearsquestion asks for data to be copied as well
-
baltermia about 3 yearsThis also allows copying tables from one server to another! +1
-
Edward almost 3 yearsThis works really easily, with no surprises regarding table structure and column definitions, however in my case, it was crazy slow for a large table. 150M records x 30 columns took ~12 hrs.