I have a SQL Table suppose XYZ which contains more than 2 Million data rows. I need information for 10k data points which I hold in an excel sheet. Now I can use Join with Primary key and get the data which takes longer time as my main table in holding huge number of data rows. Any other way which could find only those 10k records within shorter time?
Thanks in Advance
Soumen
Solved! Go to Solution.
@soumen - you could embed table XYZ's SQL inside the Dynamic Input tool, and pass the 10k ID's (from excel file) to this SQL during run-time to fetch data only for those records. In other words, once you embed table XYZ's SQL inside the Dynamic Input tool, use its Modify SQL query option to replace a specific string of this SQL (in this case your 10k ID's).
The suggested solution is somewhat dependent on your database type (specially with Oracle), and how well is your SQL written. Oracle I think has a limit of accepting only 1000 ID's in its Where clause - in which case you can embed the Dynamic Input inside a Batch Macro and pass the 10k ID's to this macro in batches of 1000.
Hope this helps.
@soumen - on second thoughts you probably could get away with simply using a simple Input tool. I have attached a simple proof of concept here to help you setup your solution. In here:
This should hopefully get you the solution you need.
Like most things in Alteryx, there are multiple ways to achieve the same goal - you could use In-Databse tools as well, but try this one to begin with.
I tried recreating the workflow you explained however I am running into difficulty with the macro part. I am getting no output after running the workflow as if it stopped running and didn't even run through the macro. When I run the batch macro on it's own it seems to work so I don't think it is the SQL that is incorrect. Would you be able to show what your macro would look like?
Here is my workflow:
Here is what my macro looks like:
Hi @chinnashleyn1, below is a screenshot of how I had setup the macro
In reference to your workflow setup, can you confirm if the field you are passing into the macro is replacing the appropriate section of the SQL inside the macro?
@AbhilashR, I was able to get it to work using your example. Thanks for your help.