Hello,
I want to push en Excel file as a temporary table in a database (Snowflake for e.g.) and then use in an Input tool.
My objective is to use this temporary table to limit the scope of a big query.
Thanks for your help,
Best regards,
matyohan
If it is always the same kind of Data, I suggest you create a table that will be used for that purpose (not temporary then) since creating a table in Alteryx can be tricky (key columns, format...) and you will need to elaborate a macro to automatically create and adjust a new table fitting a new type of data to use it after.
Once that table is created, input your data with INPUT DATA
Then export them to your table with the OUTPUT DATA module. Select "Write to file or Database" / "Data Sources" and chose Snowflake or other.
Then add the name of the Table (verify it after, it should be the same format as a sheet for Excel (ex: connection_name|||table_name).
Then chose "Delete Data and Expand", then you won't need to delete the table yourself to clear it.
Then use your table_name as a join in the query.
If needed, between input and output, you perhaps will need to put a recordID to have unique lines, then don't forget to create that column in the table to host that Key Column.
Hi @matyohan,
You could use the Data Stream In tool to load it as a temporary table, then use the In-DB tools afterwards to build your query!
Data Stream In Tool | Alteryx Help
-Alex
Thanks for your help. To be more precise, I need to do these two actions in the same workflow.
@ZEL, the datatype can change.
@alexnajm, I don't know how to have an Input tool after a Data Stream tool in a same workflow, is it possible?
@matyohan since it's in the same workflow, you don't need another Input Data - just use the anchor after that Data Stream In tool as your data going forward.
@alexnajmI need to use this temporary table (for example, a list of customers) in the join of an sql query and avoid loading all the records
Right, so do something like this:
The Connect In-DB would just be the other table!
You can use Join inDB right after the Data Stream in
Or you can Create a table (not temprary then) and use that name in the query
You can use the same name every time and use the overwrite/drop mode, then your table will be updated with new data
@matyohan for clarity, the Connect In-DB tool won't read in any records (unless you put down a Browse In-DB tool) - the In-Database tools are helping to build a query and when it's done it will execute against the database it's connected to. So this solution would work to limit your database on the Excel file!
@matyohan make sure to accept one or more of the responses as a solution if this answered your question - thank you!
I know this is months old but I think it’s similar to what I have been trying and jsut posted about on here. I have 2 different Oracle databases, 1 will query to pull in a certain amount of records and ideally I’d like that to act as my temp table to run my second query/database against to pull in 2 additional fields. I don’t want to query the whole database and a regular dynamic input takes way too long. Could I do this same thing as you guys outlined?
As long as you can connect to that database through the IN-DB tools, then sure!
