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

sql-server add tag

13 years ago Just a learner (imported from SE)

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;

xxxxxxxxxx
 
create table #table_name(    column1 int,    column2 varchar(200)    ...)​insert into #table_nameexecute 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 if there is an easier way to do this?

Top Answer

13 years ago aaron bertrand (imported from SE)

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;

xxxxxxxxxx
 
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
);

xxxxxxxxxx
 
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. 😃

Answer #2

13 years ago Martin Smith (imported from SE)

If the procedure just returns one result set and the ad hoc distributed queries option is enabled.

SELECT * 
INTO #T 
FROM OPENROWSET('SQLNCLI', 
                'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                 'SET FMTONLY OFF;EXEC sp_who')

xxxxxxxxxx
 
SELECT * INTO #T FROM OPENROWSET('SQLNCLI',                 'Server=(local)\MSSQL2008;Trusted_Connection=yes;',                 'SET FMTONLY OFF;EXEC sp_who')

Or you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLNCLI', @datasrc = @@servername

SELECT *
INTO  #T
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC sp_who')

xxxxxxxxxx
 
EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',                        @provider = 'SQLNCLI', @datasrc = @@servername​SELECT *INTO  #TFROM OPENQUERY(LOCALSERVER,                'SET FMTONLY OFF;               EXEC sp_who')

2 Answers