Merge data from one table to anoter with FK

Question

Dear SO :)

I have to merge data from one table to another. Both of these tables have the same structure, something like this:

Id (PK*) | Name | ParetnId (FK to PK*, it's a tree)

What I'm trying to use:

• Plain insert/update - too long SQL, FK/PK issues (when I copy row with ParentId to not existing row)
• merge operator - much shorter, but the same issues

I know that some guys temporary turn constraints off, copy data and turn constraints on again. In my case I trying to avoid this. (Many threads writing to these tables, and I don't know how such tricks affected to transactions)

The problem is: how to merge (or just copy) records between such tables without turning constraints off.

Answer 1

I could use some more information on the source table. I am assuming that it will not have the primary key column. If it also does not have the foreign key column, then I am assuming you can join to the target table to get the id (if so, you can eliminate the AND EXISTS clauses from below). The only trouble is that you will have to run this several times until there are no more inserts.

MERGE INTO Target AS T
  USING Source AS S
    ON S.Name = T.Name
  WHEN MATCHED AND EXISTS(
    SELECT *
      FROM Target
      WHERE Id = S.ParentID
    ) THEN
    UPDATE SET
      ParentId = S.ParentID
  WHEN NOT MATCHED BY TARGET AND EXISTS(
    SELECT *
      FROM Target
      WHERE Id = S.ParentID
    ) THEN
    INSERT VALUES(
      S.Name,
      S.ParentID
      )