1

I found this link that could be the solution to my question, but I need some clarification.

I have two DBs on one server, the one DB is a backup. I have a broken table and want to replace the table values/records with the table from the backup DB. Can I use the method from the link above, or is it only if the destination table is empty that I can use the "INSERT INTO destination"?

My goal is to overwrite the table with the backup values.

Community
  • 1
  • 1
emo
  • 137
  • 1
  • 8

1 Answers1

1

Since your goal is to replace the entire table with the table from the backup database, you can TRUNCATE the target table and then reload from the backup table using INSERT...SELECT.

You will need to be mindful of foreign key constraints. TRUNCATE is not allowed if FKs reference the table so you will need to use DELETE to empty the table instead.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Exactly, that is what I have to do, replace the entire table. I am not experienced in SQL, so this is relative new for me. How can i see if the table is dependent of FKs, and how to turn them off/delete before I do the Truncate, Insert, Select. – emo Oct 15 '14 at 19:06
  • One method to identify referencing foreign key constraints is with "EXEC sp_help 'dbo.YourTargetTable';". If FKs reference the table, just use `DELETE` instead of `TRUNCATE`. – Dan Guzman Oct 15 '14 at 19:43
  • My solution was to only copy the columns that was wrong in the original table, and not touch the ID column, so I just used 'UPDATE' and 'SELECT' and 'JOIN' where I joined my IDies. – emo Oct 16 '14 at 09:15