how to read multiple rows in Dynamic Input tool

[dipika07011988]

Hi,

I have  a situation here - 

I dont have rights on sql server to create tables. So i put the o/p, which is a list of Ids , in a text file.

Now i want to use these records in i/p tool as filter on my data.

I know dynamic i/p can do this but i am not aware of passing a list of 4k records to the i/p and using these 4k in where clause or in inner join.

Dynamic i/p reads records one at a tym so this option will be time consuming .

Can someone help on this one?

[NicoleJohnson]

Could you create batches of IDs and then combine the records using a concatenate function in the Summary tool, perhaps? And then pass those records to Dynamic Input using an IN statement in your WHERE clause.

For example, use a Tile Tool to create n number of Tiles (groups of records). Could start with maybe 100 per group and tweak from there. Then use a Summarize tool to group by Tile Number and then concatenate your IDs (separate by comma, and include ' quote marks if needed before & after the IDs if it's a string field). Then pass those concatenated strings of IDs into Dynamic Input to replace a placeholder in your WHERE clause, like "ID In (__Placeholder__). You'd indicate in the Dynamic Input that you want to replace __Placeholder__ with '123','ABC','XYZ' i.e. your concatenated list of IDs. 

You'd still be running multiple Dynamic Input queries, but there would be fewer of them, so would potentially help with the efficiency issue. 

Does that help? Let me know! 

Cheers,

NJ

[BrandonB · Accepted solution]

In your WHERE clause you could use an IN statement and then use the Group functionality in the Dynamic Input tool. That way it will collapse the values into a comma separated list and only execute one query instead of a query for every row: 

[dipika07011988]

thats quite a good work around. i will try it and post hw it goes.

[dipika07011988]

This i dint know.Ty .this shud work.