Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bbxrider
Starting Member
37 Posts |
 Posted - 2011-06-21 : 15:31:32
|
| sql server 2000 using mgmt studio 2005 expressI want to completely duplicate a table, including indexes, statistics, etc etcthe 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-21 : 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
37 Posts |
Posted - 2011-06-24 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-24 : 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.SunitaPS: No, I definitely do not do that! I was only thinking out loud!! |
|
|
|
|
|
|
|
|
complete dup of table in same db