Forum Discussion
Post ProdigyHelp! Is it possible to use Row_Number with Concurrent Inserts?
Hi arpost
Thanks for using Fabric Community.
Just wanted to share some information, if that could help you.
In Microsoft Fabric, you can generate unique identifiers in a warehouse table using a workaround technique .
Generate unique identifiers in a warehouse table in Microsoft Fabric - Microsoft Fabric | Microsoft Learn
This method ensures that each row inserted into the table has a unique identifier, even when multiple stored procedures are inserting data into the same table concurrently.
Hope this helps. Please let me know if you have any further questions.
arpostAnonymous, 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.Hi @arpost
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks