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.
| Author |
Topic |
|
Pasi
Posting Yak Master
166 Posts |
 Posted - 2015-03-28 : 00:46:58
|
HI,I have a table called Lab_results and I have backed it up to "lab_results_backup032215". how do I restore the back up to the original table. I tried :select * into Lab_results from lab_results_backup032215 but get error saying the table already exist?Thanks,Pasi |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-03-28 : 07:23:51
|
| This might work for you:insert into Lab_results select * from lab_result_backup032215But it can be a bit more tricky if your table have:- identity column- foreign constraintAlso you probably want to avoid duplicates |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-28 : 11:20:28
|
Might needDELETE Lab_results before the INSERT INTO ...If the DELETE gives errors ("You can't delete this because something else is depending on it") then you'll need an UpSert of some sort (UPDATE all the existing rows, INSERT any that don't exist, DELETE any that are NOT in the Backup). All quite complicated ...... or DROP the Foreign Key constraints, then DELETE and INSERT from backup, and then reCREATE the Foreign Key Constraints again.None of it totally straightforward ... sorry! |
|
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2015-03-29 : 23:28:12
|
Thanks Bitsmed!! you would think backing up is easy but as well as restoring it with a simple command but its not as easy as you would think!quote:
Originally posted by bitsmed
This might work for you:insert into Lab_results select * from lab_result_backup032215But it can be a bit more tricky if your table have:- identity column- foreign constraintAlso you probably want to avoid duplicates
|
|
|
|
Pasi
Posting Yak Master
166 Posts |
Posted - 2015-03-29 : 23:29:59
|
Thankss Kristen.. I guess you right no easy way.quote:
Originally posted by Kristen
Might needDELETE Lab_results before the INSERT INTO ...If the DELETE gives errors ("You can't delete this because something else is depending on it") then you'll need an UpSert of some sort (UPDATE all the existing rows, INSERT any that don't exist, DELETE any that are NOT in the Backup). All quite complicated ...... or DROP the Foreign Key constraints, then DELETE and INSERT from backup, and then reCREATE the Foreign Key Constraints again.None of it totally straightforward ... sorry!
|
|
|
|
huangchen
Starting Member
37 Posts |
Posted - 2015-04-02 : 05:50:34
|
| unspammed |
|
|
|
|
|
|
reestoring a table from backup table