This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Almost Friday, Alteryx Community. Can. You. Feel. IT!?!?!?!?!?!?!?!?
So i am just starting to get into the In-Database tools. I have a SSMS query that is really two queries, one being a query that creates a temp table, which is then itself queried. Confused yet.
So in Alteryx, I have successfully used the Connect In-DB tool to bring back the desired results of the first part of my query (the temp table part). Now, I am wondering how I would run the second part, which queries the temp table? Is it one of the In-Database tools, or am I going about doing this the wrong way? Can you create a temp table using the In-Database tools, just like you would in SSMS? Thanks.
While you can materialize the data going into the temp table, if you're not going to use that data for long you don't need to do so. If you use the in-db tools, you can stream that data and join to it with your second query. That temp table will get created without your knowledge if and when it is needed. Keep your data in-db as long as you can and don't write it out unless you've got a really good reason to do so. When you JOIN data in-db, you'll be creating a temp table.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
Mark is basically saying: don't worry about it, things will work fine :)
If you think you must have a temp table (you don't, Alteryx creates temp tables all the time for in-db), you can use the Write Data in-DB tool to create a table of your choosing or a temp one that Alteryx names ...
Take the SELECT query that you are using to run the first query, and put it in a "Connect In-DB" tool.
If your second query only uses results from the first which creates a temp table, try using the Filter, Formula, Select, and Summarize In-DB tools to recreate that query.
If your second query JOINS to your temp table, take the piece of query 2 which selects fields from tables that are NOT your temp table, and put that in a second "Connect In-DB" tool. Then, use a "Join In-DB" tool followed by a "Select In-DB" (and possibly a "Filter In-DB") tool to perform the Join you are using in your current SQL.