Insert results of a stored procedure into a temporary table

I met the same problem and here is what I did for this from [Paul's suggestion][1]. 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 + ']')


[1]:

[To see links please register here]

"Paul's suggestion"

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)

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'`

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.

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.

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

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][1]
- [sys.dm_exec_describe_first_result_set][2]
- [sp_describe_first_result_set][3]

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][4] like when dynamic T-SQL statements are executed or temporary tables are used in the stored procedure.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

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][1] 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.

[1]:

[To see links please register here]

> <h2>Easiest Solution:</h2>
>
> 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]')

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