0

An existing database has been rolled out to several clients already.

Now I developed a add on and need to transfer my newly added rows of PK-FK tables to the various target dbs. But I can't just copy the PKs as the target might already use that specific PK and therefore being in unknown state to me when writing the update script...

Any ideas apart from increasing the PK numbers on the source db by, say ten thousand, hoping it will be high enough not to interfer with the target PKs?

Thanks

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
Elementenfresser
  • 711
  • 5
  • 18
  • 1
    This question might trigger some ideas on what you can do. [Using merge..output to get mapping between source.id and target.id](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) – Mikael Eriksson Jun 02 '12 at 20:21
  • Thanks Mikael, your "output" way made me think about the relation info already there and how to keep it while transfering the rows...see below. – Elementenfresser Jun 03 '12 at 11:19

2 Answers2

0

I will probably end up

  • adding GUIDs to the tables parallel to my int PKs and FKs.
  • Filling those fields with newid() and copying those values to the FK tables.
  • Then transfer my newly added rows without the FK data (after disabling check constraints)
  • and then rectify the int FK values using my GUID references.

gosh that script is gonna take some time...

Update: Thanks to Mikael's comment I found the solution right before my eyes:

  • I will just add a new "PK shadow" column, defaulting to the PK value.
  • then remove the default (so the target db has null values there when extending the target schema)
  • So after transferring the rows (without identity insert on) I will still have references to the old PK
  • and only need to update all "broken" FKs joining the FK tables with the PK shadow column

and here are the scripts: http://www.morrenth.com/transferring-some-rows-having-.aspx

Elementenfresser
  • 711
  • 5
  • 18
0

You could use variables in your script holding the scope_identity() for every PK row and using this value in your FK table.

Elementenfresser
  • 711
  • 5
  • 18