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 |
|
boabmcb
Starting Member
2 Posts |
 Posted - 2009-09-21 : 06:21:01
|
| Hi all, I'm a bit of an SQL newbie and need some advice following a mistake I made on a Live client database this morning. I acidentally deleted data from numerous related tables containing an employee's data and need to re-insert them back to the Live db from a copy of the site I got down from late last night. For each table I deleted from I'm hoping to...1) Begin transaction. 2) lock the table. 3) disable the identity on ID column.4) make my insert from a select statement pulling data from the backup.5) re-enable the identity column.6) unlock the table.7) commit the transaction.Does this seem feasible?(I realise it will be time consuming and painfull but I need the data back to how it was id's and all).Can anyone advise if it is possible and how I would lock a table and disable the identity property to allow me to manually insert a specific int to be the identity property while preventing any other inserts to this table happening?Hope my explanations make sense, Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-21 : 06:33:53
|
set identity_insert on -- that's only for your session - no problem for othersbegin transactioninsert live_database..table_name select id_col, col2, col3 from bak_database..bak_table where ...commitset identity_insert offBut you should have a little test before doing that in live database!
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
boabmcb
Starting Member
2 Posts |
Posted - 2009-09-21 : 07:01:36
|
| Fantastic, thanks for that webfred extremely helpful !I can assure you I will be doing plenty tests before I go near the Live db again. The only thing that I remain unsure if is if it's possible to specifically lock a table so I can make my insert and prevent anyone else from doing so until I release the lock again. Need to temporarily prevent other inserts while I have the IDENTITY_INSERT ON. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-21 : 07:38:26
|
It should exactly work like you want it to work.The identity_insert is only set for your session so that anybody else can insert a new record with an auto-id.Have a try:make this:set identity_insert on begin transaction- now start a new session (a new query window is also new session) and try to insert a new recordnow go on with:insert live_database..table_name select id_col, col2, col3 from bak_database..bak_table where ...commitset identity_insert off
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
Turn off identity and lock table for insert