Thanks for all the replies...
As Sean Lange suggested, I tried with temp table (instead of temp variable).
I faced these problems.
1. I have to build the sql statement. So I am using EXEC (@DynamicSQL) statement.
I am not able to get the data from temp table
DECLARE @DynamicSQL varchar(max)
set @DynamicSQL = 'select top 100 * into #SourceData from customers'
select * from #SourceData
2. I need to get the column names of the temp table. Below statement is not helping me either...
select ',' + name
WHERE id = object_id('#SourceData')
order by name
Thanks & Regards,
For 1. that's the life of the temps in the dynamic sql ; it all ends with execution;no temps created inside of "EXEC" lives to see another sql statement.
For 2. what are you trying to accomplish..
try this , I am making a guess..
select * into #customers from dbo.customers where 1=2
-- this creates the table structure for temptable; don't use dynamic
-- after this what exactly do you want..
please be specific , while posting ..
Wondering what I would do next , when I am done with this one