Left join field from input file with SQL Query

[youngjw]

I've search high and low, and can't quite get to what I think I need.  Here is my situation.

I have a 'large' file (180K records) with 3 columns of data that looks like below.  Lets call the file ASSETS.CSV

CODE              SERIAL_NUMBER               ACCOUNT

ABC                  LK100222                            123456789

ABC                  MN012345                           123456789

ABC                  PQR12345                           123456780

Need to, effectively, use the is file a DB and left join SERIAL_NUMBER with data in an actual table.  lets call the table  PARTS_SALES

The PARTS_SALES table will have millions of rows and 00s of columns

I effectively need to be able to:

SELECT a1.serial_no, a1.col2, a1.col3, a1col3, a1.col5, b1.SERIAL_NUMBER, b1.ACCOUNT

FROM ASSETS.CSV b1

LEFT JOIN PARTS_SALES a1 ON b1.SERIAL_NUMBER = a1.serial_no

Is there a straight forward way to do this.

[OllieClarke · Accepted solution]

Hi @youngjw 
There are a number of ways of doing this, but probably the easiest is using the in-db tool palette. You can stream your csv into a temp table and then create the left join. All of this will happen in the database, so you won't need to download the millions of sales records to your designer instance unnecessarily

[youngjw]

Thanks Ollie, I have some learning to do on the stream in tool, but this looks exactly like what I need to do.  thanks!

[OllieClarke]

@youngjw the Stream In/Out tools let you switch modalities and take data into the database, or into memory. Yours is the perfect use-case for the Stream In tool

[youngjw]

Thanks Ollie!

So my situation is this.  In a large corporation and not a lot of flexibility to get write access to a DB, especially co-located on the table with 00s of millions of rows.

Is it fair to say that it is not possible to more simply just use Join tool to join two Input Data tools (one linked to input file, 00k rows and the other a sql statment 00sM rows). ?

[gdj20305]

What other option can be used when one does not have access to create a temp table on the database?  I am not able to use the DB stream in tool, but need to join an excel input file to the SQL query to limit the data.  

[leefarrell]

Load Data:

ASSETS.CSV: Use Alteryx’s Input Data tool.
PARTS_SALES Table: Connect to your database.
Join Data:

In Alteryx: Use the Join tool. Connect ASSETS.CSV to the left input and PARTS_SALES to the right. Join on SERIAL_NUMBER = serial_no.
In SQL: Run:
sql
Copy code
SELECT a1.serial_no, a1.col2, a1.col3, a1.col5, b1.SERIAL_NUMBER, b1.ACCOUNT
FROM ASSETS b1
LEFT JOIN PARTS_SALES a1 ON b1.SERIAL_NUMBER = a1.serial_no;
Output:

In Alteryx: Use the Output Data tool.
In SQL: Execute the query to get results.