March 25, 2009 at 9:13 am
Hi,
I did a backup of a table by doing a select * into #temp from table. If for whatever reason, I need to restore the data from the temp table onto the regular table, how do I do that? I don't mean doing a ROLLBACK, the situation might be for example, if I discovered a problem later.
I would want to UPDATE the regular table, not do an INSERT because that would probably just insert duplicate records. Something like this:
update table set ..... select * from #temp
is there anyway to do that? Thanks in advance.
March 25, 2009 at 9:19 am
if its sql 2005 then use snapshot of database before making any change.
if change is not good or wrong just restore that snapshot. its very fast since it only restore change that were made.
if you want to use table backup then do it like this. create new table same schema as original donot create temp table. move data to new table. run you change script .
if you dont like the change the script made then truncate original table and restore data from new table.
if its DML then you have to ask developers to provide you roll back script. usually thats how it suppose to be.
:crazy: :alien:
Umar Iqbal
March 25, 2009 at 11:12 am
remember that the moment you disconnect, your #tmp table will be DELETED;
it lasts only as long as your session...so you are creating a permenant table on the same database, right? you just plan on deletingi it later, so it's not a #temp table, correct?
you'll have to write that long monotonous update statemtn for each column:
UPDATE A --alias...it's identified below in the FROM
SET A.COL1 = B.COL1,
A.COL2 = B.COL2,
.....
A.COL54 = B.COL54
FROM REALTABLE A
INNER JOIN BACKUPTABLE B ON A.PKID = B.PKID
rnunez (3/25/2009)
Hi,I did a backup of a table by doing a select * into #temp from table. If for whatever reason, I need to restore the data from the temp table onto the regular table, how do I do that? I don't mean doing a ROLLBACK, the situation might be for example, if I discovered a problem later.
I would want to UPDATE the regular table, not do an INSERT because that would probably just insert duplicate records. Something like this:
update table set ..... select * from #temp
is there anyway to do that? Thanks in advance.
Lowell
March 25, 2009 at 11:17 am
Thanks Umar, what would be the script to restore the data? Would it be like Lowell's post?
March 25, 2009 at 11:20 am
Thanks Lowell, I was hoping for a temp table, but if I want to disconnect, I guess not use it..just maybe while I have the session connected. Thanks for the script, might have to use it sometime.
March 25, 2009 at 11:37 am
lowell script is fine you just need to introduce conditional clause in case other ppl are inserting at the same time you are inserting.
:crazy: :alien:
Umar Iqbal
March 25, 2009 at 11:57 am
adding a WHERE clause to check if any of the columns changed might be beneficial, but it's going to do a table scan regardless. like
WHERE A.Col1 <> B.Col1
OR A.Col2 <> B.Col2
...etc
that's going to lock the table for the duration of the update, so anyone inserting or updating would be help up until the update completed anyway;
inserts would be doing a new row, and might not get hit with a table lock caused by an UPDATE; it really depends on the real schema and whether there is a PK index on he table
what additional conditions were you recommending to add?
Lowell
March 25, 2009 at 12:03 pm
I won't need to worry about other ppl doing updates/inserts to the table. My changes happen when I take the application(that hits the database) down and this would be during off hours. Thanks, though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply