This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm working on a project which will result in rows being inserted into a SQL table.
Prior to inserting the rows I must first find the last primary key used in the table. This requires me to go to another table since the 'Identity' on the primary key is set to 'false'. I then apply the next key in sequence to the first row on my insert file and increment it as needed.
So far, so good.
The problem is that the table I'm writing to is hit a lot by another process. Meaning that after the flow takes 5 or so minutes to run the keys I've assigned to my rows are now taken resulting in primary key violations when attempting to insert the rows.
Our DBAs don't want me using the In-Database tools, so I must use the Input tool using a 'Table or Query'. Selection below.
Select bav.[FIELD1], bav.[FIELD2]
From [DATABASENAME].[TABLENAME] bav
From what I can tell this is firing off rather early. Is there a means by which I can delay this so that it fires last? It won't eliminate my problem entirely, but would lessen the chances of the duplicate key problem from happening. I'm also open to other suggestions.
If the progress can be timed with records passing through preceding tools, you could place your query in a Dynamic Input. Set the Dynamic Input Modify SQL settings to "Pass a Field to the Output" which won't have any effect on the query besides appending a field to the results, but it will require that the field is populated before the query is executed.