Insert to temp table

Question

Hello...

this is my table structure  (all fields are varchar)


           Ddate                                     brandcode               tsum 

10/13/2009  Shift2	84803573	J  2304
10/13/2009  Shift2	84819544	I  2000,  K  2000
10/13/2009  Shift2	84822855	F  936
10/13/2009  Shift2	84823375	B  80,  C  80,  D  80
10/13/2009  Shift2	84823539	A  103,  G  103
10/13/2009  Shift2	84823968	H  250
10/13/2009  Shift2	84825713	E  71
10/29/2009  Shift1	84803573	D  35
10/29/2009  Shift1	84804154	A  23
10/29/2009  Shift1	84823529	J  838

I wrote a store procedure  like this

declare

 

@collist nvarchar(max)

select

 

@collist=coalesce(@collist+', ','')

 

+'['+Ddate+']'

from

 

(select distinct [Ddate]

 

from All_view) x

order

 

by [Ddate]

declare

 

@sql nvarchar(max);

set

 

@sql='

with PivotInput as

(

select brandcode,Ddate,tsum from All_view

)

select brandcode,'

 

+@collist+' from PivotInput

pivot (max(tsum)

for Ddate in

('

 

+@collist+')) PivotOutput'

 

 

exec sp_executesql @sql

to get a result like this (Pivot)



Brand code,10/13/2009  Shift3,10/13/2009  Shift2,10/13/2009  Shift1,..........etc






How can i save this details into a dynamic temp table. because the number of fields may vary each time. Can i create a view ?

because I can not access the data from vb.net throgh this stored procedure. I can execute from SQL sever management studio.




Please give me a solution.


Thanks
Matt

Answer 1

You can add an INTO clause into your SELECT statement.

In other words...

set @sql=' 
with PivotInput as
(
select brandcode,Ddate,tsum from All_view
)
select brandcode,'+@collist+' INTO ##SomeTempTable from PivotInput 
pivot (max(tsum)
for Ddate in
('+@collist+')) PivotOutput' 

I created a "global" temp table (i.e. one that starts with ##), because you're calling sp_executesql, and when that creates a (normal) temp table, it will not be visible to the calling procedure, but a "global" temp table will be visible.

Make sure that the temp table does not exist first.

---

--Brad (My Blog)

Answer 2

Brad,

If we use a global temporary table (same with permanent), then you can not execute the sp concurrently, it will yield an error.


AMB

Answer 3

Brad,

If we use a global temporary table (same with permanent), then you can not execute the sp concurrently, it will yield an error.


AMB

Good point...

Perhaps one could compose a "unique" table name using a GUID of some kind? 

Something like:

DECLARE @MyTableName varchar(100)
SET @MyTableName = '##SomePrefix_' + CONVERT(varchar(100),NEWID())

Not an absolute guarantee, but better I suppose...

---

--Brad (My Blog)

Answer 4

Thank you Brad and AMB

But still i can not access the data from the stored procedure.
It is showing when i execute on Sql sever studio, but not out side.

declare @collist nvarchar(max)
select @collist=coalesce(@collist+', ','')
      +'['+Ddate+']'
from (select distinct [Ddate] 
      from  All_view) x
order by [Ddate] 

declare @sql nvarchar(max);
set @sql=' 
with PivotInput as
(
select brandcode,Ddate,tsum from All_view
)
select brandcode,'+@collist+' INTO ##SomeTempTable from PivotInput 
pivot (max(tsum)
for Ddate in
('+@collist+')) PivotOutput'
exec sp_executesql @sql
select * from ##SomeTempTable
 
	

Answer 5

Prior to execution do a PRINT to verify the assembled code. If OK, you can uncomment the last 2 lines and optionally comment out the PRINT.

declare @collist nvarchar(max)
select @collist=coalesce(@collist+', ','')
      +'['+Ddate+']'
from (select distinct [Ddate] 
      from  All_view) x
order by [Ddate] 

declare @sql nvarchar(max);
set @sql=' 
with PivotInput as
(
select brandcode,Ddate,tsum from All_view
)
select brandcode,'+@collist+' INTO ##SomeTempTable from PivotInput 
pivot (max(tsum)
for Ddate in
('+@collist+')) PivotOutput'
PRINT @sql
-- exec sp_executesql @sql
-- select * from ##SomeTempTable

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com