copy a table - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

copy a table

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2006-03-16 : 13:03:24

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 13:29:16

First of all, good job on anticipating the need for a backup!

you can either:

insert myTable (<colList>)
select <sameColumnList> from myTable_temp

or

drop myTable
go
exec sp_rename 'myTable_temp', 'myTable'

Be One with the Optimizer
TG

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2006-03-16 : 13:37:33

so.. if I do an insert into.. I don't have a choice but listing out all of the columns??? There are quite a few of them!!!!

as for your 2nd suggestion, I thought that if I dropped the original table I would lose my indexes and keys and such...

I'm just trying to figure out how to easily backup a couple of tables - so I can easily roll back if need be.. without restoring the whole database.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 13:42:10

you can omit the column names since they are identical.

you can script out the table structure, then do a global find/replace on the object name to apply the script to the other table.

referential integrety is going to be the biggest hassle. If you have foreign keys in other tables referencing this table then you'll either need to drop them all and reapply them or back up all their data as well.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources