Pass datatable into a where query in SQL

Hi,

I have an xlsx file in the following layout:

ID

123
456
789
987
654
321

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?

For example:

Select *
From [table1]
Where id like ‘123,456,789,…’

for that you can use an IN statement, like:
select * from table where id IN (121,2121)

1 Like

Hi @qwerty1

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.

1 Like

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

string var

assign var = var + ",'" + row("ID").ToString + "'"

Something like this

1 Like

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!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.