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 · Accepted solution]

@cito 

This is as close as I can come.

[apathetichell]

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.

[cito]

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

[cito]

@dYoast 

I am trying to replicate your solution but somethin I am not doing well. It should read about 1.500 records from Teradata server but as you can see from the picture below it loaded over 12k records and I stopped it.

Maybe I did something wrong in dynamic input, the second picture below.

Update: 3rd picture below. In my original Teradata query, I put WHERE condition and tried to replace it with "concat_serial_number_set" that I created in summarize tool. 0 records were loaded. Maybe "concat_serial_number_set" needs to be in format '123','234','345' instead of 123,234,345 since this is SQL IN condition. 

[cito]

@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

[dYoast]

@cito

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.

[apathetichell]

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

[cito]

@dYoast 

Again...The picture below is my SQL query from dynamic input (it is Teradata sql) and I put WHERE condition for "serial_number). The first pucture.

After this, I tried with the option " SQL: Update WHERE clause.... the second picture below. This passed successfully but my final output contains the serial numbers in format: "123" and "234" not my real numbers.

Is there a way to show me this as an example in the flow, please, as I can't figure it out for 2 days. :)  

[apathetichell]

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

[cito]

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