Multiple Queries In-Database

[robertfishel]

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. 

[Claje]

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.

[ak2018]

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.