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 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 2

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 3

When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.

Run the stored procedure once and go back and remove the SQL code you just added. Now, you'll have an empty table matching the stored procedure's result. You could either "script table as create" for a temporary table or simply insert directly into that table.

Answer 4

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

Answer 5

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 6

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;