Insert results of a stored procedure into a tempor

Question

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select *
into tmpBusLine
from BusinessLine

I am trying the same, but using a stored procedure that returns data, is not quite the same.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

Answer 1

This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.

Answer 2

If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.

Answer 3

Well, you do have to create a temp table, but it doesn't have to have the right schema....I've created a stored procedure that modifies an existing temp table so that it has the required columns with the right data type and order (dropping all existing columns, adding new columns):

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Note this won't work if sys.dm_exec_describe_first_result_set_for_object can't determine the results of the stored procedure (for instance if it uses a temp table).

Answer 4

If the OPENROWSET is causing you issues, there is another way from 2012 onwards; make use of sys.dm_exec_describe_first_result_set_for_object, as mentioned here: Retrieve column names and types of a stored procedure?

First, create this stored procedure to generate the SQL for the temporary

CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
    @ProcedureName  nvarchar(128),
    @TableName      nvarchar(128),
    @SQL            nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +''  + ','
        FROM sys.dm_exec_describe_first_result_set_for_object
        (
          OBJECT_ID(@ProcedureName), 
          NULL
        );

--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))    
SET @SQL =  @SQL +')'

To use the procedure, call it in the following way:

DECLARE     @SQL    NVARCHAR(MAX)

exec dbo.usp_GetStoredProcTableDefinition
    @ProcedureName='dbo.usp_YourProcedure',
    @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC    [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable

Note that I'm using a global temporary table. That's because using EXEC to run the dynamic SQL creates its own session, so an ordinary temporary table would be out of scope to any subsequent code. If a global temporary table is a problem, you can use an ordinary temporary table, but any subsequent SQL would need to be dynamic, that is, also executed by the EXEC statement.

Answer 5

If you're lucky enough to have SQL 2012 or higher, you can use dm_exec_describe_first_result_set_for_object

I have just edited the sql provided by gotqn. Thanks gotqn.

This creates a global temp table with name same as procedure name. The temp table can later be used as required. Just don't forget to drop it before re-executing.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end

Answer 6

In order to insert the first record set of a stored procedure into a temporary table you need to know the following:

1. only the first row set of the stored procedure can be inserted into a temporary table
2. the stored procedure must not execute dynamic T-SQL statement (sp_executesql)
3. you need to define the structure of the temporary table first

The above may look as limitation, but IMHO it perfectly makes sense - if you are using sp_executesql you can once return two columns and once ten, and if you have multiple result sets, you cannot insert them into several tables as well - you can insert maximum in two table in one T-SQL statement (using OUTPUT clause and no triggers).

So, the issue is mainly how to define the temporary table structure before performing the EXEC ... INTO ... statement.

• sys.dm_exec_describe_first_result_set_for_object
• sys.dm_exec_describe_first_result_set
• sp_describe_first_result_set

The first works with OBJECT_ID while the second and the third works with Ad-hoc queries as well. I prefer to use the DMV instead of the sp as you can use CROSS APPLY and build the temporary table definitions for multiple procedures at the same time.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

Also, pay attention to the system_type_name field as it can be very useful. It stores the column complete definition. For, example:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

and you can use it directly in most of the cases to create the table definition.

So, I think in most of the cases (if the stored procedure match certain criteria) you can easily build dynamic statements for solving such issues (create the temporary table, insert the stored procedure result in it, do what you need with the data).

---

Note, that the objects above fail to define the first result set data in some cases like when dynamic T-SQL statements are executed or temporary tables are used in the stored procedure.