What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

462 Asked by CarolBower in Salesforce , Asked on Apr 16, 2021

Many times I need to write something like the following when dealing with SQL Server.

create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute some_stored_procedure;But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is any easy way to do this. How sql server create temp table?

Answered by Carolyn Buckland

Here is the Syntax to create a Temporary Table is given below:

To Create Temporary Table: CREATE TABLE #EmpDetails (id INT, name VARCHAR(25))

To Insert Values Into Temporary Table: INSERT INTO #EmpDetails VALUES (01, 'Lalit'), (02, 'Atharva')

To Select Values from Temporary Table: SELECT * FROM #EmpDetails.

Result: OR,

In SQL Server 2012 and above, you can use sys.dm_exec_describe_first_result_set locally, assuming the result set you are after is the first result:

DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9) + name + ' ' + system_type_name FROM sys.dm_exec_describe_first_result_set('sp_who', NULL, 1); SELECT @sql = N'CREATE TABLE #f (' + STUFF(@sql, 1, 1, N'') + ' );'; PRINT @sql;

Result:

  CREATE TABLE #f ( spid smallint, ecid smallint, status nchar(30), loginame nvarchar(128), hostname nchar(128), blk char(5), dbname nvarchar(128), cmd nchar(16), request_id int );

Note there is a limitation: if your stored procedure creates #temp tables, the metadata functionality does not work. This is why I did not use sp_who2.

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Your Answer