ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Delay 'Table or Query' in Input Tool

G_WOLAK
7 - Meteor

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.

echuong1
Alteryx
Alteryx

Have you tried using the Block Until Done tool to delay that part of the workflow from running?

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Block-Until-Done/ta-p/...

 

You can also try creating a chained app. Essentially, the first part of your workflow would run and get all of your values. Once that workflow has completed, it would run the second workflow.

G_WOLAK
7 - Meteor

I am using the 'Block Until Done' tools throughout the flow although I plan on playing around with the order of them to see if there is anything I can do to mitigate this issue.

 

Didn't know if there were better solutions.

CharlieS
17 - Castor
17 - Castor

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. 

Labels