Challenging issue with database keys

In 99% of such cases I would only consider doing something like this with a SQLExecutor using a single transaction block.

The issue with any other solution is what would happen if e.g. the workspace or anything else fails when only half of the update has been made. The data would be in an unknown state and could possibly create serious problems further down the line. By using a database transaction you're guaranteed that either the whole update goes through, or nothing at all.

I think for me there are two things I don't understand:

1. Are you dropping the SQL Server table each time (so you migrate all of the Access table every time) or are you just doing a change-only update (so you migrate just changed records)?
2. If you just migrate changed records, how do you know which ConcessionID was the old contract? Without contractid I don't see how you can ever tell which was the old contract.

I mean, somehow you need a link between the old contractid and the new ConcessionID. Otherwise I don't see how you can go back to an old record and update it. Maybe one solution is to have a third table to act as a lookup. It has contractid and ConcessionID in it. So whenever you write a new record to SQL Server, you write contractid and ConcessionID to the lookup table. Now you have a means to make the join that you propose. Or rather, to retrieve the new ConcessionID and update it (maybe a writer with a Where clause, or maybe using SQL as David suggests).

Thank you for the suggestions. I got this to work by using an sqlexecutor to get the current identity-value from the consession-table and a counter for creating the next identity values. Then I used a featurejoiner to join the appropriate records, before writing to the table using insert into identity column.