Import data from two different servers based on WHILE condition

[cito]

I had a similar question last year but there was one system/server (Teradata).

Today, for this question, I have two different systems/servers: Microsoft SQL Server and Teradata.

My first input comes from SQL Server where I have 50 records:

SELECT *
FROM dbo.table1

In this table (dbo.table1) I have e.g. 20 "model_numbers" and now I need to bring some additional fields from another server and table (Teradata) but this table has more than 30 million records. I would like to avoid input/load all records from the Teradata table and would like to create a condition in Teradata input WHERE "model_numbers" (from Teradata) will bi IN ("model_numbers" from SQL table).

SELECT *
FROM ted.table2
WHERE model_numbers IN (SELECT DISTINCT model_numbers FROM dbo.table1)

Is this possible to achieve in Alteryx when we have two different systems/servers?

Thank you in advance. 

[dYoast]

I think it may be possible, but I don't have a way to fully test.

For the second input (dynamic input), choose to Modify SQL Query and then replace a specific string.

[cito]

Not sure if I understood your solution, I will try, but my current flow looks like in the picture below.

t1 comes from SQL server and t2 comes from Teradata server.

[apathetichell]

I'm actually doing  a user group meeting on this topic (dynamic where clauses) tomorrow. Basically what you need to do is compile the dbo1 (small data set) details first - you then do some summarize tool/formula tool/union tool magic to create a new SQL statement which takes your dbo1  output and turns it into a Where 'x' in (my dbo1 values) SQL statement. You feed that in with a connection to Dynamic Input In-DB to dbo2- and you are good.

[cito]

Some screenshot of the flow how it should look like?

[apathetichell]

I'll see what I can dig up . There are a variety of different ways you can configure it... but seeing the screen shot without understanding the configuration settings might not really help.

[apathetichell]

The blue arrow represents your smaller data set. The other arrow represents your big data set.

[cito]

Sorry, I tried to replicate the flow based on your picture but still can't figure out all the steps.
Attached is an example, and yes, there is no way to replicate two different servers but let's imagine that like in the flow.

[dYoast]

@cito 

This example does not have two databases, but maybe will help.

Output from the formula:

SQL Query of the Dynamic Input Data Source Template:

EXEC usp_MyProcedure '2021-04-10', '2021-04-11'

Modification of the query:

The end result is that the SQL Query takes the values from the formula tool and uses them instead of the original values.

In your case, you would get the list of values from the first table and use a Summarize tool to make a string.

That string would then replace the string of your IN clause for the query of the second table.

I hope this helps.

[cito]

@dYoast 

Sorry man, but I don't understand anything from the pictures, really. If you are able to help me with some "live" flow based on my example I would really appreciate it.