Create temporary table from output of dynamic SQL

Question

Create temporary table from output of dynamic SQL

sandesh_krishnan

Old Hand

Points: 358
More actions
December 30, 2015 at 5:51 am

#399517

Hi,
Is there an option to create a temporary table based on the output of dynamic SQL?
Example : 'Select * Into #TEMP_TABLE FROM '+ DYNAMIC_SQL
I have tried option of OPENROWSET, but this feature is not allowed in the current environment due to security issues. Is there any other alternative to this?
Additional details :
We are trying to create an audit framework which will run queries as per a particular schedule. These results will be sent in an email. The queries that are run, is different every time because they go after different tables and yield different results.
As mentioned, the creation of the output table from the query provided can be achieved through OPENROWSET, but its not allowed due to security concerns. We have tried .net approach as well and it is working. We wanted to see if this can be achieved in a "pure" SQL fashion.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No.

Temp tables are dropped when the scope they're created in ends, and that's the dynamic SQL. The temp table is created, but is dropped when the dynamic SQL ends. To persist it outside, you will need to do an explicit CREATE TABLE and insert ... exec.

Also, while you haven't given much details, be very careful that you don't have a sql injection vulnerability in that dynamic SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

sandesh_krishnan (12/30/2015)
Hi,
Is there an option to create a temporary table based on the output of dynamic SQL?
Example : 'Select * Into #TEMP_TABLE FROM '+ DYNAMIC_SQL
I have tried option of OPENROWSET, but this feature is not allowed in the current environment due to security issues. Is there any other alternative to this?

Yes, but with some risk. You can create the dynamic SQL to load a Global Temporary Table (one that uses the ## prefix). The problem with that is concurrency. Unlike local Temp Tables (that use the # prefix), all sessions can see the Global Temp Table. If you have another proc running that uses the same name Global Temp Table, you will have a clash.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101105 More actions · Answer 3

You can create a non-temp table in tempdb.

CREATE TABLE tempdb.dbo.table_name ( ... )

That table will go away only when you explicitly drop it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sandesh_krishnan Old Hand Points: 358 More actions · Answer 4

Additional details :

We are trying to create an audit framework which will run queries as per a particular schedule. These results will be sent in an email. The queries that are run, is different every time because they go after different tables and yield different results.

As mentioned, the creation of the output table from the query provided can be achieved through OPENROWSET, but its not allowed due to security concerns. We have tried .net approach as well and it is working. We wanted to see if this can be achieved in a "pure" SQL fashion.

Thanks in advance.