Making a copy of a sql table within same DB
-
Hi. I would like to make a simple backup of just 1 sql table. What is the easiest way to do this and to also capture indexes, keys, etc.
I have used DTS to do this but wanted to know if there was a 'shortcut' ???
Juanita
-
If the new table doesn't exist - this will create it:
SELECT *
INTO NewTableName
FROM TableName
If the new table already exists:
INSERT INTO NewTableName
SELECT *
FROM TableName
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I don't think Select Into configures indexes and keys.
You may want to script the table first with all keys and indexes but WITHOUT Drop Table option so you would not accedentally delete your original table, in the script replace a table name with a new name, run the script to create a new table. The use Jim P.'s second syntax to get data in the case the new table already exists.
Yelena
Regards,Yelena Varsha
-
I don't think Select Into configures indexes and keys.
True - but it all depends what you're trying to do and the size of the table involved.
If I'm just trying to take a snapshot before a mass update or delete/insert, I can live without the indexes. If he is trying to use it for OLAP then the scripting and insert into makes sense. (But he probably needs to be careful about carring FK's across, sometimes.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Hi. Actually I am taking a snapshot before a big update. So the create and insert would be fine. But let's say I had to do a quick restore, Since I only save the data and not the indexes, keys, etc. how would I get that back? That is why i was trying to save a picture of the table with everything in tact.
Juanita.
-
Are you actually dropping the original table?
In the "Select into" you get all the data. As long as you don't drop the original table, if you want to back off the the import, you would just do:
TRUNCATE TABLE TableName
GO
INSERT INTO TableName
SELECT *
FROM NewTableName
GO
The indexes, keys etc. should automattically regenerate ont the re-isnert.
If you want to be absolutely sure then you have to script the table and indexes throught the enterprise mansger or from the query anlyzer, change them all to the NewTableName
run the scripts and then do:
INSERT INTO NewTableName
SELECT *
FROM TableName
GO
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
HI. I would only drop the original table if the massive update was a failure and would want to put the table back to its' original state. I'm just doing the copy so if i have to restore i don't have to use an entire database backup to get the file back.
Juanita
-
Hi. I think the select into is the best option. If the update fails I'd then truncate the original table and re-populate it with the data from the select into table. Indexes should not be dropped on the original table so everything should be intact after you place the data back in the table.
MD
Marvin Dillard
Senior Consultant
Claraview Inc -
Be aware that you will not be able to truncate the original table if it is referenced by any foreign keys which could cause you problems when rolling back any changes.
Lloyd
-
If the table does not exist I would:
SELECT TOP 0 *
INTO NewTableName
FROM TableName
INSERT INTO NewTableName
SELECT *
FROM TableName
(There are annoying locks being held in tempdb (That blocks/hangs for example SQL Enterprise Manager) during 'SELECT ... INTO ... FROM... ' which would make it good to first create the structure and then insert the data.)
For applying the indexes I would select the original table in EM, check all checkboxes for indexes and constraints I want to maintain, click preview, copy and paste it in SQL Query Analyzer, Do a search and replace of the table name with the new table name and execute it. Remember to do this operation AFTER you have copied the data into the table (if you have enough space).
Happy hunting, Hanslindgren
-
Thank you everyone for all your help and suggestions. I learned alot from all of your input!!
Juanita
-
"I have used DTS to do this but wanted to know if there was a 'shortcut' ???"
Please...how much easier can it get then the copy object task?
As for "select into", this obviously won't work as it doesn't copy indexes or constraints. Hans is totally right about the annoying temdb lock, though, ignore him at your own peril.
cl
Signature is NULL
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply