March 16, 2006 at 9:42 am
This is probably a dumb question.. but here goes.
I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.
What I 'thought' i could to was this:
select * into mytable_temp from mytable
and then to roll back..
Select * into mytable from mytable_temp
When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????
March 16, 2006 at 10:01 am
when you try to copy back the data to original table, the error will be thrown up because select into creates the destination table on the fly.The correct way to do this is --
insert into mytable
select * from mytable_temp
truncate the table before inserting the values.
March 16, 2006 at 10:38 am
OK. I have to truncate the original table before moving data back into it.. but what is the syntax to copy the data back to the original table??
March 16, 2006 at 11:57 am
was able to figure this much out with a little help....
TO ROLL BACK
truncate table mytable
insert into mytable
select * from mytable_temp
Now I'm getting this error:
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'pcb00100' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Do i have to do something differently since I'm working with an identity column? I just want to be able to preserve the table data and roll back if I need to..
March 17, 2006 at 5:25 am
Try this:
truncate table mytable
SET IDENTITY_INSERT mytable ON
insert into mytable
select * from mytable_temp
SET IDENTITY_INSERT mytable OFF
March 17, 2006 at 7:24 am
When I do this.. I'm still getting the below error....
truncate table mytable
go
set identity_insert mytable on
go
insert into mytable
select * from mytable_temp
go
set identity_insert mytable off
go
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'mytable' can only be specified when a column list is used and IDENTITY_INSERT is ON.
March 17, 2006 at 7:48 am
As the message indicates, you must list the table fields in the insert clause.
INSERT INTO mytable
(field1, field2, field3)
SELECT * FROM mytable_temp
A handy way to do this in QA is to right click on the table and select "Script Object to Clipboard - Insert" and paste it into the query window, then replace the VALUES line with your SELECT query.
Hope that helps
Mark
March 20, 2006 at 6:22 am
How about delete original table
rename the copy to the original table name
March 20, 2006 at 1:52 pm
I really can't just delete the original table.. I need to be able to roll back if the rows being inserted in the original table have any adverse effects.. Plus, if I delete the original table.. I'll lose my Keys, contraints, formatting etc.. this is what I ended up doing..
select * into new_table from orig_table
rollback...
Truncate orig table and then DTS the data back in from the new table..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply