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.
Solved! Go to Solution.
Two thoughts:
You can write something like:
SELECT a.name FROM (SELECT name FROM agentinformation) a
In a regular Input Data tool and Alteryx will handle it fine. I have sometimes found that Alteryx has a hard time with these types of queries in the In-Database tools (since from a technical perspective, the In-Database tools create very similar subqueries, so they don't always nest appropriately).
With regards to write permissions needed for temp tables - I'm not an expert on SQL Server or any other database technology, but my understanding is that typically the ability to create temp tables and the ability to write to a permanent table are two separated permissions, so it is possible to have access to use In-Database without having permission to create a New Table.
How would the SELECT In-DB tool help? It does not allow to insert a query or such. I am struggling to understand what would come next after the Data Stream InDB Tool. Pardon my ignorance as am still a novice.
I used the Data Stream In-DB Tool to ingest records that will go into a temp table that I have to join with a bunch of datasets that are otherwise huge to pull records that match and then bring them out. Sounds simple but I haven't been able to accomplish this. The only way I could do is with this layout: However, am not sure how it will perform once I ingest more than 50 files with over 2GB of records instead of just 1 file I do currently with about 150k rows.