Storing Dynamic Query Output in Temp Table

Question

Storing Dynamic Query Output in Temp Table

sarath.tata

SSCommitted

Points: 1640
More actions
April 6, 2015 at 10:14 am

#316136

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What about inserting it into a global temp table? Would that cause issues?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sarath.tata SSCommitted Points: 1640 More actions · Answer 2

I can't use global temp variables as it differs per request.

if my query is in @sql, so far I could find the way to pre-create temp table with the same columns of the @sql output and using INSERT INTO statement.

I'm not able to figure out the way to do something like SELECT * INTO so that my columns are dynamically generated in Temp table.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

sarath.tata (4/6/2015)
I can't use global temp variables as it differs per request.

He said global temp table (e.g. ##temp). Global temp tables can be referenced inside/outside of a dynamic SQL script.

if my query is in @sql, so far I could find the way to pre-create temp table with the same columns of the @sql output and using INSERT INTO statement.
I'm not able to figure out the way to do something like SELECT * INTO so that my columns are dynamically generated in Temp table.

I think that this will help you accomplish what you are trying to do (note my comments)

DECLARE @dsql_selectvarchar(100) = 'SELECT c1, c2, c3'; -- variable that holds a dynamic SELECT statement

DECLARE @dsql_fromvarchar(100) = ' FROM (VALUES (11,22,33)) t(c1,c2,c3);'; -- Simulating a table with data

DECLARE @dsql_gtempvarchar(100) = '##yourglobaltemp'; -- a variable that holds the name of the global temp table to be dynamically generated via Dynamic SQL query

EXEC(@dsql_select + ' INTO '+ @dsql_gtemp + @dsql_from); -- for brevity I'm ignoring the issue of SQL Injection

SELECT * FROM ##yourglobaltemp; -- prove that it's working

--cleanup

DROP TABLE ##yourglobaltemp;

Also note that, after you have created the global temp table you can use tempdb.INFORMATION_SCHEMA.COLUMNS to get the names and other information about the columns in your newly created temp table for further scripting. E.g. for ##yourglobaltemp this query would get you information about it's columns:

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = '##yourglobaltemp';

Edit: code formatting

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

sandesh_krishnan Old Hand Points: 358 More actions · Answer 4

Couple of approaches that can be tried :

1. Use OPENROWSET to capture results of @sql. the @sql should be executed within a stored procedure and results generated. OPENROWSET will not be allowed in all databases due to security concerns.

2. Use XML to capture results of the SQL.

Hope this helps.