Create Temp Table at Runtime in Sql Server

Sometimes we have to create temp table in SQL Server for getting data at same scope.
For creating Local Temp Table then use single '#'   like this

create table #temptbl
(ID int, name varchar(200),address varchar(500))

insert into #temptbl
values(1,'testname','test address')

If you want to create global temp table for accessing in multiple scope or session then use '##' 

create table ##Globaltemptbl
(ID int, name varchar(200),address varchar(500))

insert into ##Globaltemptbl
values(1,'testname1','test address1')

you can get global temp table from any sql scope or session. Temp Tables are physically stored in TempDB. If you want to create Temp Table  when columns are not known at run time then use below code

SELECT * INTO #TestTbl FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes;',
'EXEC PROCNAME')
-- Select Table
SELECT * FROM #TestTbl 

Temp Table is created at run time. if you want parameter with procedure just pass the param value with procedure use below code

SELECT * INTO #TestTbl FROM OPENROWSET('SQLNCLI', 'Server=SERVERNAME;Trusted_Connection=yes;',
'EXEC PROCNAME PARAMETERVALUE')
-- Select Table
SELECT * FROM #TestTbl 

if you get any error executing above sql query then configure sql for OPENROWSET

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

"Tricks always work"