copy table data back and forth

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..

truncate table mytable

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??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..

truncate table mytable

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??

You can do it by -

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable

insert into mytable
select * from mytable_temp
go
drop table mytable_temp|||Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

one more question though.. how do I roill back when one column is an identity column? I get this error:

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.|||Thanks so much! that will work for me.. just couldn't get the syntax right!!!!

one more question though.. how do I roill back when one column is an identity column? I get this error:

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.

Just do this -

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable
go
set identity_insert mytable on
go
insert into mytable(id,name)
select id,name from mytable_temp
go
set identity_insert mytable off
go
drop table mytable_temp

That will work for you.|||AWESOME thank you so much!|||When I do this.. I'm still getting the below error...
(not sure what you mean by id,name in your code.. here's mine.. )

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.|||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.insert into PCB00100 (column_list)
select * from PCB00100_temp
go-PatP|||Thanks Pat!

Is there anyway to generate the column list so I can paste it into my code? There are ALOT of columns : )|||Hi

This might help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63300|||Hi

This might help:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63300You are a deviant soul... I knew that there was something I liked about you from the git-go! ;)

-PatP|||You are a deviant soul... I knew that there was something I liked about you from the git-go! ;)
Lol - thanks (I think) :D

BTW - ignore the bit where jhermiz says he would rather type out every column name for a table (twice) than use my script. He's just fooling around - we go back a long way and actually I am very highly respected at SQL Team...ahem ;)|||I am very highly respected at SQL Team...ahem ;)

that and 35 cents will get you a washington post.|||that and 35 cents will get you a washington post.I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim :)|||I have not the faintest idea what that means - perhaps the meaning fades as it crosses the Atlantic or perhaps I am just being dim :)

My guess is that Thrasy is expressing how he doesn't like SQL Team :o) .. I remember back (a while ago) he posted something on SQL Team that started a war of words. I was shocked at how edgy some of the folks at SQL Team were. To his defense (not that he needs it ;) ) Thrasy didn't do anything that seemed offensive but the reaction from some SQL Team members was very immature.|||In all fairness I did make a little joke to start it off and the ringleader Tara did back off. On a rare occassion I still post over there but the place does leave a bad taste in my mouth.

If 35 cents gets you a washington post newspaper. And respect at SQLTeam + 35 cents gets you a washington post. Then what does respect at SQLTeam equal.

X + Y = Z
X = Z
Y = 0|||Thanks Sean - faintly embarrassed that you thought it necessary to explain and then also prove mathematically lol

I tracked down your thread - quite right - they were a bit like humourless piranhas...but I was sorry to see you later lost your cool and resorted to SQL based comedy... oh Sean - how could you?

BTW - like your new SQL Team sig :D|||BTW- to add a more simple answer to the (final) question - you can also right click the table in the object browser of query analyser -> Script Object to New Window as and then choose your weapon of choice. Select gives you the easiest-to-copy-and-paste column list.