Insert results of a stored procedure into a temporary table

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

A few years late to the question, but I needed something like this for some quick and dirty code generation. I believe as others have stated it is just easier to define the temp table up front, but this method should work for simple stored procedure queries or sql statments.

This will be a little convoluted, but it borrows from the contributors here as well as Paul White's solution from DBA Stack Exchange Get stored procedure result column-types. Again, to reiterate this approach & example is not designed for processes in a multi user environment. In this case the table definition is being set for a short time in a global temp table for reference by a code generation template process.

I haven't fully tested this so there may be caveats so you may want to go to the MSDN link in Paul White's answer. This applies to SQL Server 2012 and higher.

First use the stored procedure sp_describe_first_result_set which resembles Oracle's describe.

This will evaluate the first row of the first result set so if your stored procedure or statement returns multiple queries it will only describe the first result.

I created a stored proc to break down the tasks that returns a single field to select from to create the temp table definition.

CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
     @sql NVARCHAR(4000)
    ,@table_name VARCHAR(100)
    ,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TempTableDefinition NVARCHAR(MAX)
    DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)

    DECLARE @ResultDefinition TABLE (  --The View Definition per MSDN
      is_hidden         bit NOT NULL
    , column_ordinal    int NOT NULL
    , [name]            sysname NULL
    , is_nullable       bit NOT NULL
    , system_type_id    int NOT NULL
    , system_type_name  nvarchar(256) NULL
    , max_length        smallint NOT NULL
    , [precision]       tinyint NOT NULL
    , scale             tinyint NOT NULL
    , collation_name    sysname NULL    
    , user_type_id      int NULL
    , user_type_database    sysname NULL    
    , user_type_schema  sysname NULL
    , user_type_name    sysname NULL    
    , assembly_qualified_type_name      nvarchar(4000)  
    , xml_collection_id         int NULL
    , xml_collection_database   sysname NULL    
    , xml_collection_schema     sysname NULL    
    , xml_collection_name       sysname NULL
    , is_xml_document           bit NOT NULL            
    , is_case_sensitive         bit NOT NULL            
    , is_fixed_length_clr_type  bit NOT NULL    
    , source_server             sysname NULL            
    , source_database           sysname NULL
    , source_schema             sysname NULL
    , source_table              sysname NULL
    , source_column             sysname NULL
    , is_identity_column        bit NULL
    , is_part_of_unique_key     bit NULL
    , is_updateable             bit NULL
    , is_computed_column        bit NULL
    , is_sparse_column_set      bit NULL
    , ordinal_in_order_by_list  smallint NULL   
    , order_by_is_descending    smallint NULL   
    , order_by_list_length      smallint NULL
    , tds_type_id               int NOT NULL
    , tds_length                int NOT NULL
    , tds_collation_id          int NULL
    , tds_collation_sort_id     tinyint NULL
    )

    --Insert the description into table variable    
    INSERT @ResultDefinition
    EXEC sp_describe_first_result_set @sql

    --Now Build the string to create the table via union select statement
    ;WITH STMT AS (
        SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
        UNION ALL

        SELECT 
         CONCAT(
                CASE column_ordinal
                    WHEN 1 THEN '     ' ELSE '   , ' END  --Determines if comma should precede
                , QUOTENAME([name]) , '   ', system_type_name  -- Column Name and SQL TYPE
                ,CASE is_nullable 
                    WHEN 0 THEN '   NOT NULL' ELSE '   NULL' END --NULLABLE CONSTRAINT          
               ) AS TextVal
        FROM @ResultDefinition WHERE is_hidden = 0  -- May not be needed
        UNION ALL

        SELECT N');' + @NewLine
    ) 

    --Now Combine the rows to a single String
    SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT

    SELECT @TableDefinition = @TempTableDefinition
END

The conundrum is that you need to use a global table, but you need to make it unique enough so you can drop and create from it frequently without worrying about a collision.
In the example I used a Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) for the global variable replacing the hyphens with underscore

DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql can be a stored procedure name like dbo.foo without parameters

DECLARE @TableDef NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet] 
    @sql, @GlobalTempTable, @TableDef OUTPUT

--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef 

--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
    EXEC sp_executesql @sql 

--Select the results into your undefined Temp Table from the Global Table
SELECT * 
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT * FROM #MyTempTable

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

Again, I have only tested it with simple stored procedure queries and simple queries so your mileage may vary. Hope this helps someone.

Answer 2

If you're lucky enough to have SQL Server 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 3

I had a similar requirement. One proc that processed data (in this case uploaded spreadsheets) and put the data into a table with column headers based on the first row. The proc that called this would not know what the schema was for the table being passed back to it. During my searching for a solution, this thread kept popping up so I thought I would reply.

Instead of trying to pass a table from one proc to another, I used the scope of temp tables.

ALTER PROCEDURE [dbo].[ParentProc]

AS
BEGIN
    IF(OBJECT_ID('tempdb..#LoadTable')) IS NOT NULL DROP TABLE #LoadTable

    CREATE TABLE #LoadTable(DUMMY nVarChar(max))

    EXEC [dbo].[FetchExcelSheet] 'C:\Temp\ExcelVerizonUploadTest.csv'

    -- Process your data here

    IF(OBJECT_ID('tempdb..##LoadTable')) IS NOT NULL DROP TABLE #LoadTable

END

This is the proc that loads the data from the excel sheets.

ALTER PROCEDURE [dbo].[FetchExcelSheet]
    -- Add the parameters for the stored procedure here
        @pURL nVarChar(max) 

AS
BEGIN

    SET NOCOUNT ON;


    -- ************ IMPORTANT ****************************
    -- IF YOU  PUT  A TICKET IN AND HAVE NOT READ THIS. I WILL MOCK YOU!!!!
    -- #LOADTABLE MUST BE CREATED IN THE PARENT PROC.
    --CREATE TABLE #LoadTable(DUMMY nVarChar(max))

    IF(OBJECT_ID('tempdb..#ColumnNames')) IS NOT NULL  DROP TABLE 
    #ColumnNames

    IF(OBJECT_ID('tempdb..#FirstRowHold')) IS NOT NULL DROP TABLE 
     #FirstRowHold
         

    CREATE TABLE #FirstRowHold(DUMMY nVarChar(max))

    -- Move the first row of the csv file into the HoldTable
    DECLARE @pFirstRowSQL nVarChar(max)        
    SET @pFirstRowSQL = '          
    BULK INSERT #FirstRowHold   FROM ''' + @pURL + '''
    WITH (FORMAT = ''CSV'',
                FirstRow = 1,
                LastRow = 1,
                ROWTERMINATOR = ''\n'');'
    EXEC (@pFirstRowSQL)


    -- Split the string and put it into individual rows in the NameHold table.
    SELECT * INTO #ColumnNames FROM STRING_SPLIT((SELECT TOP 1 DUMMY FROM #FirstRowHold) ,',')
    
    -- Add Identity in for serialization and duplication check
    ALTER TABLE #ColumnNames ADD ID INT IDENTITY

    -- Append a serial number to duplicate values.
    UPDATE #ColumnNames     
        SET value = value + CAST(ID AS nVarChar(max)) WHERE VALUE IN (
    SELECT value from #ColumnNames GROUP BY value HAVING COUNT(*) > 1)
    
    
    DECLARE @TempID nVArChar(max)
    
    
    DECLARE @StripID nVarChar(max)
     
     -- Cycle through the ColumnName table and add each as a column
     WHILE(SELECT COUNT(*) FROM #ColumnNames) > 0
     BEGIN

             SELECT TOP 1 @TempID = (SELECT TOP 1 VALUE FROM #ColumnNames) 

             SET @StripID = dbo.[udf-Str-Strip-Control](@TempID)

            EXEC ('ALTER TABLE #LoadTable ADD  [' +  @StripID + '] nVarChar(max)')

            DELETE  FROM #ColumnNames WHERE value = @TempID

     END

      -- Remove this column or the bulk insert will fail.
    ALTER TABLE #LoadTable DROP COLUMN DUMMY
    
    -- Upload the rest of the table
    EXEC( 'BULK INSERT #LoadTable   FROM ''' + @pURL + ''' WITH (FORMAT = ''CSV'', FIRSTROW = 2, ROWTERMINATOR = ''\n'')')

    IF(OBJECT_ID('tempdb..#ColumnNames')) IS NOT NULL  DROP TABLE #ColumnNames

    IF(OBJECT_ID('tempdb..#FirstRowHold')) IS NOT NULL DROP TABLE #FirstRowHold



END

#LoadTable is a temp table so there is no conflict if multiple users run this proc at the same time. I also did not have to use openrowset or system tables to reconstruct the table since its already done when the calling proc gets control back from the FetchExcelSheet.

Only thing to remember is to create the #LoadTable in the parent each time you create a new one. Other than that this has been working great for us.

Answer 4

Easiest solution: In case your can modify the SP SLIGHTLY.

I wanted to share this because I tried all the above and nothing worked for security reason so I ended up doing this very simple modification.

Whatever your SP returns INSERT those results in a temp table with ## (## makes it global temp, # would make it local only) So at the end of your SP, when the results are produced, transform:

SELECT...;

by:

INSERT INTO ##temp;

SELECT...;

SELECT * FROM ##temp;

Now in whatever script you want to use the results, simply use ##temp as a regular table after executing

EXEC...;

Answer 5

Old post but useful. In addition to other answers, one can also capture the output of a stored procedure in to a table-variable (as asker wants to avoid the use of #temp-table), I therefore created a fully working example using @table-variable:

--Note: separately run each section at a time:
--Section-1: create stored proc that outputs a result-set:
    CREATE OR ALTER PROCEDURE TestSP AS
    BEGIN
        SELECT database_id, name from sys.databases
    END
------------------------
--Section-2: create @table-variable with columns matching the output (alternatively #Temp table can be used):
    DECLARE @t TABLE (did INT, dname VARCHAR(99))

    --Capture the output:
    insert into @t
    exec TestSP

    --View the captured output:
    select * from @t
------------------------
--Section-3: Clean-up
    DROP PROCEDURE TestSP

HTH.

Answer 6

1. First select an empty result set into the temptable, just to have it created.
2. Run the exec SP into TABLE

SELECT TOP 0 * INTO [temp_table] FROM [Table]; 

EXEC [StoredProcedure] INTO [temp_table];

Answer 7

First, modify your stored procedure to save the end results in to a temp table. By doing this we are creating a table matching with the SP output fields. And then have a select statement to save that temp table to a any table name. Then execute the SP as explained in step 2

Step 1: modify your stored procedure to save the end results in to a temp table

[your stored procedure] 

into #table_temp //this will insert the data to a temp table

from  #table_temp 

select * into SP_Output_Table_1 from #table_temp //this will save data to a actual table

Step 2: Execute the SP as below that will insert records to your table

Insert SP_Output_Table_1
EXE  You_SP_Nane @Parameter1 = 52, @parameter2 =1

Answer 8

Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')

Answer 9

Here is my T-SQL with parameters

--require one time execution if not configured before
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

--require one time execution if not configured before
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO


--the query
DECLARE @param1 int = 1, @param2 int = 2
DECLARE @SQLStr varchar(max) = 'SELECT * INTO #MyTempTable
                                FROM OPENROWSET(''SQLNCLI'',  
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec StoredProcedureName '+ CAST(@param1 AS varchar(15)) +','+ CAST(@param2 AS varchar(15)) +''') AS a ;
 select * from #MyTempTable;
 drop table #MyTempTable        
';
EXECUTE(@SQLStr);

Answer 10

1. I'm creating a table with the following schema and data.

2. Create a stored procedure.

3. Now I know what the result of my procedure is, so I am performing the following query.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
    CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
   
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF
    VALUES (10, 5, 1, NULL)
    SET IDENTITY_INSERT [dbo].[tblTestingTree] On
   
   
    create procedure GetDate
    as
    begin
        select Id,ParentId from tblTestingTree
    end
   
    create table tbltemp
    (
        id int,
        ParentId int
    )
    insert into tbltemp
    exec GetDate
   
    select * from tbltemp;
   

Answer 11

If you want to do it without first declaring the temporary table, you could try creating a user-defined function rather than a stored procedure and make that user-defined function return a table. Alternatively, if you want to use the stored procedure, try something like this:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Answer 12

If you let dynamic SQL create a temp table, this table is owned by the Dynamic SQL connection, as opposed to the connection your stored procedure is called from.

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;

DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

SELECT * FROM #Pivoted;

Msg 208, Level 16, State 0 Invalid object name '#Pivoted'. This is because #Pivoted is owned by the Dynamic SQL connection. So the last instruction

SELECT * FROM #Pivoted

fails.

One way to not face this issue is to make sure all references to #Pivoted are made from inside the dynamic query itself:

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;


DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

Answer 13

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

DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = '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(@storedProcname),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 stored procedure as stated before.

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

Answer 14

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 table:

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 15

After searching around I found a way to create a temp table dynamically for any stored procedure without using OPENROWSET or OPENQUERY using a generic schema of Stored Procedure's result definition especially when you are not database Administrator.

Sql server has a buit-in proc sp_describe_first_result_set that can provide you with schema of any procedures resultset. I created a schema table from results of this procedure and manually set all the field to NULLABLE.

declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters 
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)

-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden  bit  NULL, column_ordinal   int  NULL, name sysname NULL, is_nullable   bit  NULL, system_type_id   int  NULL, system_type_name nvarchar(256) NULL,
max_length  smallint  NULL, precision   tinyint  NULL,  scale   tinyint  NULL,  collation_name  sysname NULL, user_type_id  int NULL, user_type_database    sysname NULL,
user_type_schema    sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name   nvarchar(4000),xml_collection_id    int NULL,xml_collection_database    sysname NULL,
xml_collection_schema   sysname NULL,xml_collection_name    sysname NULL,is_xml_document    bit  NULL,is_case_sensitive bit  NULL,is_fixed_length_clr_type  bit  NULL,
source_server   sysname NULL,source_database    sysname NULL,source_schema  sysname NULL,source_table   sysname NULL,source_column  sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key   bit NULL,is_updateable  bit NULL,is_computed_column bit NULL,is_sparse_column_set   bit NULL,ordinal_in_order_by_list   smallint NULL,
order_by_list_length    smallint NULL,order_by_is_descending    smallint NULL,tds_type_id   int  NULL,tds_length    int  NULL,tds_collation_id  int NULL,
tds_collation_sort_id   tinyint NULL)


-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0

-- Create a query to generate and populate a global temp table from above results
select 
@qry = 'Create table ##t(' +
stuff(  
    (select ',' + name + ' '+ system_type_name + ' NULL'
    from #d d For XML Path, TYPE)
    .value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')

insert into ##t 
Exec '+@procname+' ' + @param

Exec sp_executesql @qry

-- Use below global temp table to query the data as you may
select * from ##t

-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d 

Developed and tested on Sql Server version - Microsoft SQL Server 2016 (RTM) - 13.0.1601.5(Build 17134:)

You can tweak the schema for your SQL server version that you are using (if needed).

Answer 16

It's a simple 2 step process: - create a temporary table - Insert into the temporary table.

Code to perform the same:

CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable 
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;

Answer 17

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 18

Easiest Solution:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

If you don't know the schema then you can do the following. Please note that there are severe security risks in this method.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

Answer 19

This is an answer to a slightly modified version of your question. If you can abandon the use of a stored procedure for a user-defined function, you can use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.

Here's a good quick article on it and other user-defined functions. If you still have a driving need for a stored procedure, you can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function.

So for instance, you'd have an inline table-valued user-defined function to get a list of customers for a particular region:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

You can then call this function to get what your results a such:

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

If you still need a stored procedure, then wrap the function as such:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

The use of OPENROWSET has been suggested, but this is not what the OPENROWSET function was intended to be used for (From Books Online):

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.

Answer 20

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.

Answer 21

If the query doesn't contain parameter, use OpenQuery else use OpenRowset.

Basic thing would be to create schema as per stored procedure and insert into that table. e.g.:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc

Answer 22

If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs.

Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").

The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there.

Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")

After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields.

Step 3: Copy the data columns & types into a create table statement

I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.

Step 4: Insert into the newly created table

Now you'll have a query that's like the other solutions described in this thread.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

This technique can also be used to convert a temp table (#temp) to a table variable (@temp). While this may be more steps than just writing the create table statement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.

Answer 23

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 24

I would do the following

1. Create (convert SP to) a UDF (Table value UDF).

2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'

Answer 25

Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a with temp as ... and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.

For example,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

Answer 26

I met the same problem and here is what I did for this from Paul's suggestion. The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

Answer 27

I found Passing Arrays/DataTables into Stored Procedures which might give you another idea on how you might go solving your problem.

The link suggests to use an Image type parameter to pass into the stored procedure. Then in the stored procedure, the image is transformed into a table variable containing the original data.

Maybe there is a way this can be used with a temporary table.

Answer 28

This stored proc does the job:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

It's a slight rework of this: Insert stored procedure results into table so that it actually works.

If you want it to work with a temporary table then you will need to use a ##GLOBAL table and drop it afterwards.

Answer 29

In SQL Server 2005 you can use INSERT INTO ... EXEC to insert the result of a stored procedure into a table. From MSDN's INSERT documentation (for SQL Server 2000, in fact):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

Answer 30

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

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

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable