Dynamic Query Results Into A Temp Table Or Table Variable

I have a stored procedure that uses sp_executesql to generate a result set, the number of columns in the result can vary but will be in the form of Col1 Col2 Col3 etc. I need to ge

Solution 1:

I have found a solution that works for me with the help of @SQLMenace in this post T-SQL Dynamic SQL and Temp Tables

In short, I need to create a #temp table in normal SQL first, then I can alter the structure using further dynamic SQL statements. In this example @colcount is set to 6. This will be determined by another stored proc when I implement this.

IF object_id('tempdb..#myTemp') ISNOTNULLDROPTABLE #myTemp

CREATETABLE #myTemp (id intIDENTITY(1,1) )
DECLARE@cmd nvarchar(max)
DECLARE@colcountintSET@colcount=6DECLARE@counterintSET@counter=0
WHILE @counter<@colcountBEGINSET@counter=@counter+1SET@cmd='ALTER TABLE #myTemp  ADD col'+CAST(@counterASvarchar(4)) +' NVARCHAR(MAX)'EXEC(@cmd)
    ENDINSERTINTO #myTemp 
EXEC myProc @param1, @param2, @param3SELECT*FROM #myTemp

Solution 2:

IS there any reason you can't do something like:

SELECT*INTO #MyTempTable
FROM MyResultSet

SELECT INTO doesn't require an explicit field list.

Solution 3:

You can use global temp tables whose names are 'uniquified' by the SPID of the creating process. This can allow you to avoid stomping on other global temp tables created by other connections.

Just make sure to clean them up when you're done... :)