-1

I am copying the data to temporary table using select statement which produces the result of pivoting.

My procedure is: 

alter procedure proc11 AS

create Table #Temp (Software varchar(max), Ver varchar(max))

declare @cols varchar(max)
declare @colTabl varchar(max)
declare @alter varchar(max)

set @cols=STUFF((select distinct  ',[' + Ltrim(rtrim(YEAR(Dt))) +']' from temp FOR XML PATH('')),1,1,'');

set @colTabl=STUFF((select distinct  '[' + Ltrim(rtrim(YEAR(Dt))) +'] int,' from temp FOR XML PATH('')),1,0,'');

set @colTabl= LEFT(@colTabl,len(@colTabl)-1);

set @alter = 'alter table #Temp add  '+ @colTabl;

exec(@alter)

EXEC('select * into #Temp from 
(select YEAR(Dt)[year],Software,Ver from temp)T
Pivot(count([year]) for [year] in ('+@cols+'))PVT')

But the statement select * from #Temp returns no record.

How do I copy these records.

Please help. Thanks in advance.

Mohemmad K
  • 759
  • 7
  • 29
  • 68

3 Answers3

0

It's because '#' temporary tables are kept just for the duration of current connection, and visible only in it's scope. Even if you create a temp table, open another tab in SSMS and try to use it, you you'll see "table does not exist" error. What happens is, that your EXEC statement create the #Temp table, and when it finieshes, the table is immediately dropped. Not only that - #Temp table from CREATE statement, and #Temp from EXEC statement are in fact different tables! Try creating two #temp tables in different SSMS tabs and you'll see they're in fact not the same.

You could use global temp tables. Just change the name to ##Temp and it should work. Run these two examples.

This won't work:

EXEC('select * into #Temp from (select ''something'' as x) x');
select * from #Temp

And this one will (when you run it second time you'll notice "table already exists" error:

EXEC('select * into ##Temp from (select ''something'' as x) x');
select * from ##Temp
AdamL
  • 10,453
  • 5
  • 46
  • 68
0
EXEC('insert #Temp select * from 
(select YEAR(Dt)[year],Software,Ver from temp)T 
Pivot(count([year]) for [year] in ('+@cols+'))PVT order by Software')
Mohemmad K
  • 759
  • 7
  • 29
  • 68
0

Or simply remove the INSERT from the dynamic portion:

INSERT INTO #temp
EXEC(select * from 
    (select YEAR(Dt)[year],Software,Ver from temp)T 
    Pivot(count([year]) for [year] in ('+@cols+'))PVT order by Software')
Simon Adcock
  • 3,448
  • 3
  • 23
  • 41
Don DuMond
  • 1