Pass LIST of Values from One Connection to Another. Cross Connection is impractically slow.

Toad Data Point
1

Firstly, I have never been able to get even the most simple cross connections to even finish.

Cross Connection query in ONE environment, returns instantly. Join that query to a small table in a 2nd Environment and limit it in the where clause to a SINGLE value and it never returns. I never wait beyond 30 minutes for it to finish that is. End of story for me.

I want to query and retrieve a list of IDs in Environment A and then query by those IDs in Environment B.

I am not able to import data into Production environment B so I can’t create my own tables.

Is there a way using automation to pass a list of values to be used in a query without having to connect outside Environment B?

variables will store only a single value correct?

T.D.P. v4.3 PRO

Thanks.

2

Cross-connection queries can be optimized by using subqueries. See this article. https://community.toadworld.com/products/toad-data-point/b/weblog/archive/2012/11/21/how-to-write-a-cross-database-query But in the end it will take some extra time because the data is coming from two different locations and merging on your pc.

In automation you can use the LoopDataset variable to query one database and use variable values in another. This example video shows this. https://www.youtube.com/watch?v=MYEFZYz05mQ

If you don’t want to use one field value at a time you can instead loop and concatenate the IN statement --> (xx,xx,xx) and then use in second activity.