| Author |
 Topic  |
|
|
bbxrider
Starting Member
USA
32 Posts |
 Posted - 06/21/2011 : 15:31:32
|
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
5152 Posts |
Posted - 06/21/2011 : 15:52:38
|
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. |
 |
|
|
bbxrider
Starting Member
USA
32 Posts |
Posted - 06/24/2011 : 12:53:41
|
| 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/24/2011 : 14:08:23
|
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!! |
|
|
| |
Topic |
|
|
|
complete dup of table in same db
