0

I am stuck on how to replace all the data in a table that already has relationship.

  • I have to tables, Company and Contact. and the PrimaryKeys of these two tables are related to other tables.
  • Before, I do anything with Contact, I made a copy of Contact table (select * INTO ContactBK from Contact)
  • After I modified some data in Contact, now I like to replace all the data from ContactBK (orginal) back to Contact, but I could not.
  • I have tried to use Import in Server Management Studio and select "replace exists data", but it failed. I also can not delete all the data in Contact table and replace, because the ContactID is tied to others tables.
John Saunders
  • 160,644
  • 26
  • 247
  • 397
Milacay
  • 1,407
  • 7
  • 32
  • 56
  • Did you try separately inserting, updating, and deleting as appropriate? – mbeckish Jun 28 '13 at 20:36
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Jun 28 '13 at 20:37
  • To drop and recreate the foreign keys you can use this script http://stackoverflow.com/a/14995032/284111 – Andrew Savinykh Jun 28 '13 at 20:49
  • Thank you for a quick response. I will try as I described below. Worst case, I will rebuild the relationship again after replaced the data. – Milacay Jun 28 '13 at 20:54

1 Answers1

1

You could do a update from the ContactBAK table using a join approach. If the records are essentially the same with a few fields modified, this should work. For example:

UPDATE c SET c.FirstName = bak.FirstName FROM Contacts c
LEFT JOIN ContactsBAK bak ON c.ContactID = bak.ContactID

You'll have to modify the fields you want to update and match on the joins of course.

Russell Shingleton
  • 3,176
  • 1
  • 21
  • 29
  • Thanks for replay, I inserted a bunch of bogus records in the Contact table, modified some records, so you suggestion is only work for update this existing records with the same contactID. Maybe I need to delete those bogus records that are not match with ContactBK, then use our suggestions. Thanks again – Milacay Jun 28 '13 at 20:52
  • The bogus records will not be affected by the join update as they have nothing to join against in the BAK table. I would just delete them. – Russell Shingleton Jun 28 '13 at 21:11