SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 complete dup of table in same db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbxrider
Starting Member

USA
36 Posts

Posted - 06/21/2011 :  15:31:32  Show Profile  Reply with Quote
sql server 2000 using mgmt studio 2005 express
I want to completely duplicate a table, including indexes, statistics, etc etc
the insert and copy stmts I've tried so far don't get everything and I'm not seeing something like an export/import in the ms menu somewhere

surely there is some way to do this?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/21/2011 :  15:52:38  Show Profile  Reply with Quote
Do it in 2 steps. First create the table, and then do the insert.

To create the table, go to Brett's blog (http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx) and look up his FAQ section where he is describing how to script a table with the indexes. Script the table, change the table name, index names etc. and run it to create the table.

In step to, do a insert into NewTable select * from OldTable.

Couple of caveats though. Doing this would not copy the statistics. I don't know of a way to copy the statistics, if that is possible at all.

Second, if you have a lot of rows in the tables, the insert operation would be slow. Using Import/Export Wizard may be faster in that case.
Go to Top of Page

bbxrider
Starting Member

USA
36 Posts

Posted - 06/24/2011 :  12:53:41  Show Profile  Reply with Quote
thanks for the help, the script generator is very thorough, there are options in it for statistics, currently having problems that need to be fixed with my dba, first I was given create table authority, so it would even run, but now it runs, with a successful completion msg but the new (copied) table is not there
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/24/2011 :  14:08:23  Show Profile  Reply with Quote
If you have a development environment where you have all permissions, try it out there. Once it is working, then if it does not work as expected in the prod environment, blame everything on the DBA.

Sunita

PS: No, I definitely do not do that! I was only thinking out loud!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000