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'

EXEC (@DynamicSQL)

select * from #SourceData

2. I need to get the column names of the temp table. Below statement is not helping me either...

select ',' + name

FROM syscolumns

WHERE id = object_id('#SourceData')

order by name

Thanks & Regards,