Forum Discussion
Post ProdigyHelp! Is it possible to use Row_Number with Concurrent Inserts?
Post ProdigyAnonymous, thank you for that link. The issue is this doesn't ensure unique identifiers. If two jobs run concurrently, they insert duplicate keys. This workaround gets the max key and uses that for the row-numbering.
In the example below, the yellow items were script 1 and the red were script 2 executed almost simultaneously:
Hi arpost
1) Have you considered a control table for storing and managing that key value?
2) I agree that for concurrent load requirement, you will need additional logic to maintain and check for the high water mark value before the insert. If this is a batch process, you can load your data in parallel threads into a stage table, and then have a transform process and use the ROW_NUMBER() over to transform (built IDENTITY column) into the final table. This will be much more simplified process and more optimal throughput.
Please try this and let me know if the issue still persists.
Thanks.