Import data from two different servers based on WHILE condition

This is as close as I can come.

CitoTables.yxmd

A suggestion. Put a normal input tool and connect to your Teradata. Use the query from the dynamic input, BUT, replace the clause of ('123','234') with the string you generate from the Summarize.

If the normal input tool does not give the data you expect, then the replace in the Dynamic Input will not work either. If you get the normal Input tool working properly, then you should be able to convert it to a Dynamic Input.

I have to agree with @apathetichell ,who is much more experienced, your configuration looks OK.

All comments

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.

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.

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.

Some screenshot of the flow how it should look like?

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.

The blue arrow represents your smaller data set. The other arrow represents your big data set. totally useless dynamic input in-db sample screen grab.png

Import from two different servers with WHILE.yxmd

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.

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.

@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.

This is as close as I can come.

CitoTables.yxmd

Basically this is how your workflow should look. There are two dbs. First db feeds into second db. You link smaller db to bigger db. Screenshot (81).png

@apathetichell Do you mean that I am an guru/magician in Alteryx to conclude everything from the picture

@dYoast

This part is confusing me. When I click on "Add"--->"Replace a Specific String" in "Text to replace string" box is my original SQL query (I do not have any WHERE clause within it) from Teradata and in "Replacement field" is my concatenated field from Microsoft SQL query.

So, where I need to put (t1 comes from Microsoft SQL server)

SELECT distinct serial_number_set FROM dbo.t1

In your response with pictures, yes, you need to have the field from the concatenate in the format required for your SQL IN condition. I am confident you can manage that change.

In your response about the string replacement, if you do not have a WHERE clause, check your Input Data Source Template. The query there should be the query you wish to execute against your Teradata table - including the WHERE clause.

Another way of doing this is to use a normal Input tool. Set the SQL Query to include the WHERE clause. I would have a specific item in the clause - NOT the clause from dbo.t1.

Once you have the normal input tool working properly, right-click on it and select "Convert to Dynamic Input". Then change as necessary.

Here's my workflow if that helps. You will need named DB connections for both workflows.

multi-cloud multi-datbase workflow.yxzp

@cito- TBH your config looks pretty solid to me - I don't play around with my SQL on canvas as often but this looks like a reasonable configuration. I guess the important issues are:

Assuming your values are not actually '123'.'234' - can you triple check that you the values in your text to replace is the value you actually have in your query. If there is a tiny character off here (like an extra space) your replace won't work because it won't find the string to replace...

and what is in the concat string that you are feeding in? Ie do you have a browse tool for this variable before your dynamic input and have you checked the value you are concatenating here is the value you think you are concatenating here?

Import from two different servers with WHILE.yxmd

@apathetichell I have attached a flow as an example in some earlier posts. Is it possible to assist me based on it, since I really lost my mind with all these text help.

Thanks and sorry as I am not an expert or guru with Alteryx. Doing my best to figure it out.

Attached again my flow.

A suggestion. Put a normal input tool and connect to your Teradata. Use the query from the dynamic input, BUT, replace the clause of ('123','234') with the string you generate from the Summarize.

I have to agree with @apathetichell ,who is much more experienced, your configuration looks OK.