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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-18 : 08:59:19
|
| Andrew writes "Hi,Using Sql Server v8.I need to be able to do the following in 2 stored procedures (SP):1. Make an exact copy of a number of tables including the ID column. e.g. "Table_A" copied to "Table_A_copy" including any indexes defined. (1st stored procedure)2. At some point in the future replace "Table_A" with "Table_A_copy" including any indexes defined. (2nd stored procedure)I sticking points are:a. How to duplicate a table by only specifying the table name and not all of the fields.b. How to keep the ID field the same in the copy, so that after running SP1 and SP2 the IDs in the table remain as before running the stored procedures.c. How to duplicate index settings.I don't want to use temporary tables as the sql server may well be rebooted between running the 1st stored procedure and the 2nd stored procedure.At the moment I manually create a backup of the table and then restore it later. I'm wanting to use stored procedures so I can eventually execute these stored procedures via an appropriate cgi script on a web server which is initiated by an admin web page." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-18 : 09:06:51
|
| a) select * into tbl2 from tbl1b) That should do itc) Use dmo to script the indexes, change the table and index names then run it. Otherwise you could use the system table or sp_helpindex to get the index attrubutes and build a command to create them.Some of c) would need a temp table - why are you against them?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-18 : 09:11:47
|
| It would also help to explain in more detail why you need to copy a table like this. There may be another way to meet your requirements without copying the table at all. |
|
|
|
|
|
|
Duplicate Table & Data including ID column