I have an xlsx file in the following layout:
The contents of the xlsx file is stored in DT1
I have an SQL query that takes too long to execute so rather than querying the database in a for each statement (that’ll pass in the ID individually) in a loop, is there a way that i can pass all of the IDs through in the where statement as a comma separated list that would bring back only the database results that match DT1?
Where id like ‘123,456,789,…’
for that you can use an IN statement, like:
select * from table where id IN (121,2121)
You can use the “IN” command of SQL to provide multiple values…
So what you can do is, using the datatable, create a string that contains all the ID’s and pass that as the value for the IN command through a parameter…
This will allow you to retrieve all the records that are equal to the ID’s available in the datatable you have
Ideal - i’ll try that just now & report back on how it turns out.
You can use a for each row activity to loop through the datatable…
Inside the loop, use an assign activity to concatinate all the values in the datatable to a string separated by a comma
assign var = var + ",'" + row("ID").ToString + "'"
Something like this
Worked perfectly, thank you!
I just had to add a minor adjustment to remove the first ‘,’ from var but it’s brought down my runtime from 3 mins to just 30 secs, so thank you!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.