Dynamically create local temp table columns from permanent table rows
<p><strong>BACKGROUND:</strong> I write custom SQL reports for a third-party web platform -- multiple databases, most of which are either SQL Server 2008 R2 or 2012 -- and I've run into a situation where temp tables aren't being maintained in different sections of my scripts.  I haven't been able to find out whether it's just the scope that's different, or whether the sections are using separate connections.  In any case, it's been a big problem for me because it's meant having to recreate temp tables multiple times from scratch, or having to create permanent tables when I'd rather not have to.</p>

<p>To simplify things, I wrote a stored procedure that takes the contents of a temp table (or any table, really), pivots it, and stores the columns as rows in a permanent table.  This is the permanent table: </p>

<pre><code>CREATE TABLE [dbo].[CustomTempRows] (
[who] [varchar](25) NOT NULL -- A session variable from the web server
,[dtdate] [datetime] NOT NULL -- The date the row was entered
,[stable] [varchar](100) NOT NULL -- The name of the source table
,[scolumn] [varchar](100) NOT NULL -- The name of the source column
,[irow] [int] NOT NULL -- The row number from the source table
,[icolumn] [int] NOT NULL -- The column number from the source table
,[itype] [int] NOT NULL -- Whether or not the column is a string, date, or number
,[dvalue] [numeric](24, 8) NULL -- The value from the source table if it's a number.
,[svalue] [nvarchar](max) NULL -- The value from the source table if it's a string.
,[dtvalue] [datetime] NULL -- The value from the source table if it's a date.
)  
</code></pre>

<p>The procedure takes each column of each row in the temp table and inserts it as a row in the <code>CustomTempRows</code> table.  It requires that the temp table have an identity column called <code>TID</code> in order to determine the row number, which goes into <code>irow</code>.  For simplicity, it converts values to nvarchar, numeric, or datetime -- I believe this should be okay in 99% of cases.  For reference, here is the procedure:</p>

<pre><code>create procedure Custom_Table_Columns_to_Rows (@who varchar(max), @dtdate datetime, @stablename varchar(max), @breverse int)
as
BEGIN
/* NOTES

1. Temp table MUST have an identity column called TID
2. The procedure strips out timestamp and sysname columns

*/

declare @temptable varchar(100)
set @temptable = '##' + convert(varchar,@@SPID) 

if @breverse = 0 /* This parameter indicates that we are inserting to the custom table 
                rather than selecting.  Not sure if selecting is actually possible
                in stored procedure */
BEGIN

/* Clean up the custom table */
delete customtemprows where (who = @who and stable = @stablename) or datediff(mi,dtdate,getdate() )&gt;60

/* Dynamic SQL to create a global temp table and copy the user's temp table into it */
exec('if OBJECT_ID(''tempdb..' + @temptable + ''') is not null drop table ' + @temptable + ';

select * into ' + @temptable + ' from ' + @stablename )

/* Insert into customtemprows for the row and column numbers, as well as the column names and data types
(which are limited to nvarchar, numeric, and datetime -- everything is converted to one of these) 
The data columns are left blank for the moment. */
exec('
insert customtemprows (who, dtdate, stable, scolumn, irow, icolumn, itype)
select 5555
, getdate()
, ''' + @stablename + '''
, c.name
, te.tid
, c.column_id
, itype = case when t.name like ''%char%'' or t.name like ''%name%'' then 1
when t.name like (''%date%'') then 2
else 3 end

from tempdb.sys.columns c
inner join sys.types t on c.system_type_id = t.system_type_id
cross join ' + @temptable + ' te

where 
object_id = object_id(''tempdb..' + @temptable + ''')
and t.name not in (''sysname'', ''timestamp'')
and c.name&lt;&gt;''tid''
order by te.tid, c.column_id')

/* Create variables to use when running the loop */
declare @cols as table (icolumn int, scolumn varchar(100), itype int)
insert @cols
select distinct icolumn, scolumn, itype from customtemprows where who=@who and stable = @stablename and scolumn not in ('tid') order by icolumn

declare @icolumn int, @scolumn varchar(100), @itype int

select top 1 @icolumn = icolumn, @scolumn = scolumn, @itype = itype from @cols order by icolumn

/* Loop through as long as there are columns */
while exists (select * from @cols) 
BEGIN

/* If this column is a string, put it into the svalue column */
exec('update r
set svalue = t.' + @scolumn + '
FROM 
  customtemprows r
  inner join 
 (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 1
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

/* If this column is a date, put it into the dtvalue column */
exec('update r
set dtvalue = t.' + @scolumn + '
FROM 
customtemprows r
  inner join 
     (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 2
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

/* If this column is a number, put it into the dvalue column */
exec('update r
set dvalue = convert(numeric(24,10),t.' + @scolumn + ')
FROM 
  customtemprows r
  inner join 
     (SELECT tid, ' + @scolumn + '
   FROM ' + @temptable + ') t on r.irow=t.tid
where
r.itype = 3
and r.icolumn = ' + @icolumn + '
and r.who = ''' + @who + ''' 
and r.stable = ''' + @stablename+ '''' ) 

delete @cols where icolumn = @icolumn

select top 1 @icolumn = icolumn, @scolumn = scolumn, @itype = itype from @cols order by icolumn

END /* Loop */

/* Return the inserted values.  For troubleshooting */
select * from customtemprows where who = @who and stable = @stablename order by icolumn, irow
END /* @breverse = 0 */

END
</code></pre>

<p><strong>MY PROBLEM</strong> is figuring out the best way to get the data out of <code>CustomTempRows</code> and back into a temp table for use in other sections of my reports.  I've written a statement that does the job, but I'm not happy with it.  I'll explain why, but first, here's the query:</p>

<pre><code>declare @who varchar(100), @stablename varchar(100), @temptable varchar(100)

/* Set user variables */
set @who = '5555' /* Usually Session ID */
set @stablename = '#temp' /* The temp table you started with */
set @temptable = '##global' /* The name of the global temp table to create */

/* Drop the global temp table if it exists */
exec('if OBJECT_ID(''tempdb..' + @temptable + ''') is not null drop table ' + @temptable )

declare @columns varchar(max)
declare @rows as table (irow int, icolumn int, scolumn varchar(100), itype int, svalue nvarchar(max))
declare @irow int, @icolumn int, @scolumn varchar(100), @itype int, @svalue nvarchar(max), @convert varchar(50)
declare @sql varchar(max)

/* Create a list of columns to include in the temp table */
set @columns = '';
select @columns = @columns + ', ' + scolumn + ' ' + case r.itype when 1 then 'nvarchar(max)' when 2 then 'datetime' else 'numeric(24,10)' end + ' NULL'
FROM (select distinct icolumn, scolumn, itype from customtemprows 
where stable=@stablename and who = @who) r

select @columns = STUFF(@columns, 1, 2, '')

/* Create the global temp table and populate the TID column */
exec('create table ' + @temptable + '  (tid int, ' + @columns + ');

insert ' + @temptable + ' (tid)
select distinct irow from customtemprows where stable=''' + @stablename + ''' and who = ''' + @who + '''')

/* create data for the loop */
insert @rows
select irow, icolumn, scolumn, itype

/* Can't combine data types; convert everything to string */
, svalue = coalesce(svalue, convert(nvarchar,dvalue), convert(nvarchar,dtvalue)) 

from customtemprows where stable = @stablename and who = @who
order by icolumn, irow

select top 1 @irow = irow, @icolumn = icolumn, @scolumn = scolumn, @itype = itype, @svalue = svalue
/* For converting the string back to its previous data type */
, @convert = case when itype = 1 then 'convert(nvarchar(max),svalue)' 
    when itype = 2 then 'convert(datetime,svalue)'
    else 'convert(numeric(24,10),svalue)' end
     from @rows order by icolumn, irow

/* As long as there are rows */
while exists (select * from @rows)

BEGIN
set @sql = ''

/* Update the temp table one column at a time with data from the table variable */
select @sql = 'update t
set ' + @scolumn + ' = ' + @convert + '
from 
' + @temptable + ' t
inner join (
select irow, icolumn, scolumn, itype, svalue = coalesce(svalue,    convert(nvarchar,dvalue), convert(nvarchar,dtvalue)) 
from customtemprows where stable=''' + @stablename + ''' and who = ''' + @who + ''') r
on r.irow=t.tid and r.icolumn = ' + convert(varchar,@icolumn) 


exec(@sql)

delete @rows where icolumn = @icolumn

select top 1 @irow = irow, @icolumn = icolumn, @scolumn = scolumn, @itype = itype, @svalue = svalue
, @convert = case when itype = 1 then 'convert(nvarchar(max),svalue)' 
    when itype = 2 then 'convert(datetime,svalue)'
    else 'convert(numeric(24,10),svalue)' end
     from @rows order by icolumn, irow 


END /* Loop */

select * from ##global
</code></pre>

<p>It's not the most elegant solution, and I'd like to eventually replace the while loop with something set-based, but these are the bigger issues:</p>

<ol>
<li>I'd like to move this (or at least most of it) into a stored procedure so that I can share it with colleagues and not have to reproduce that big block of code every time I use it.  </li>
<li>I'd like to get the final results into a local rather than global temp table. </li>
</ol>

<p>In order to do that, though, I will have to define the temp table before calling the procedure (a limitation of the platform for which I'm developing).  Because I'm using dynamic SQL to create the table, however, I can't figure out how to create the table in the same scope where I need to use it.  </p>

<p>I would also rather use a sequential number to name the global temp table, to prevent conflicts when two users access the report at the same time.  But again, if I do that, the rest of my report code won't know the name of the table.</p>

<p><strong>MY QUESTION</strong>: Is it even possible to define a local temp table based on data in <code>CustomTempRows</code> that will be available in my report scope, and then populate that table using a stored procedure based on my long query?</p>

<p>Thank you for making it to the end of my post, and for any suggestions you may have.  It's appreciated.</p>