How to duplicate a table

[dndaughtery]

How can I duplicate a table using SQL in the Query analyzer?

 

[SQLSister]

Define duplicate? Do you just want the dat moved to another table or do you need the indexes and foreign keys?


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[klewis10367]

Right Click the table you want to Duplicate in Enterprise manager, select tasks and then generate script. Copy the code generated and place it in Query Analyzer.

 

[jbenson001]

IF you want the data with the same table structure you can use

Select *
Into <new table>
From <orig table>

 

[SQLDenis]

select * into
will give you the same fields but not the same structure

for example any identity fields, default or constraints will not be duplicated

Just keep that in mind



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Ahh good point Denis.. thanks

 

[SQLSister]

Aboslutely Dennis, that's why Iasked what the poster meant by duplicate. Because the answer is different depending on the need.

And for the record selct into will not work if you run a script to create the table first. Then you must use an insert statement to get the data.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[dndaughtery]

Thanks for all the info guys