How to SELECT * INTO [temp table] FROM [Stored Procedure]

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

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 temp table with the same structure as the output stored proc, which works fine, but it would be nice to not supply any columns.

I am using SQL Server 2005.

Answer 1

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 2000, in fact):

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

Answer 2

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')

Answer 3

could you use a function that return a table?

Answer 4

When the sproc returns a lot of columns and you do not want to manually "create" a temp table to hold the result, I've found the easiest way is to go into the sproc and add an "into" clause on the last select statement and add 1=0 to the where clause. Run the sproc once and go back and remove the sql code you just added. Now, you'll have an empty table matching the sproc's result. You could either "script table as create" for a temp table or simply insert directly into that table.

Answer 5

If you want to do it without first declaring the temp table you could try creating a user defined function rather than a stored procedure and make that user defined function return a table. Alternativly 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 6

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 7

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-Value User-Defined Function. This is essentially a stored proc (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 defiend functions. If you still have a driving need for a Stored Procedure, you can wrap the Inline Table-Value User-Defined Function with a stored procedure. The stored proc just passes parameters when it calls select * from the Inline Table-Value User-Defined Function.

So for instance, you'd have a Inline Table-Value User-Defined Function to get a list of customers for a particular region:

CREATE FUNCTION ufCustomersByRegion 
(@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 proc, 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-Value User-Defined Function in the stored proc, 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 requires Ad Hoc Query permission which 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 8

Does your stored procedure only retrieve the data or modify it too? If its used only for retrieving, you can convert the SP into a function and use the temp table 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 temp table should be used in one stmt only. You cannot do a with temp as ... and try to use it after a couple of lines of SQL. You can have multiple temp tables in one stmt for more complex queries.

For e.g.

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 9

I found a link (below) which might give you another idea on how you might go solving your problem.

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

Maybe there is a way this can be utilised with a Temp Table.

Passing Arrays/DataTables into Stored Procedures

Answer 10

you can use a common table expression to accomplish your requirements

CTE ftw!

Answer 11

Another method is to create a type and use PIPELINED to then pass back you're object. This is limited to knowing the columns however. But has the advantage of being able to do :

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))

Answer 12

Quassnoi put me most of the way their:

but one thing was missing:

*I needed to use parameters in the stored procedure.*

And OPENQUERY does not allow for this to happen:

SO I found a way to work the system and also not have to make the Table definition so rigid and redefine it inside another stored procedure (and of course take the chance it may break)!!!

YES, You can dynamically create the table definition returned from the stored procedure by using the OPENQUERY statement with bogus varaiables (as long the NO RESULT SET returns the same number of fields and in the same position as a dataset with good data).

Once the table is created....You can use exec stored procedure into the temporary table all day long.

---

AND to NOTE (as indicated above) you must enable Data Access

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

---

CODE:

declare @locCompanyId varchar(8)

declare @locDateOne datetime

declare @locDateTwo datetime

set @locDateOne = '2/11/2010'

set @locDateTwo = getdate()

--build temp table (based on bogus variable values) --because we just want the table definition and

--since openquery does not allow variable definitions...I am going to use bogus vars to get the table defintion

select * into #tempCoAttendanceRpt20100211 FROM OPENQUERY(DBASESERVER, 'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211 EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211 EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211

drop table #tempCoAttendanceRpt20100211

Thanks for the information which originally was provided... YES, FINALLY I DO NOT HAVE TO CREATE ALL THESE BOGUS (Strict) table defintions when using data from another stored procedure or database...and YES you can use parameters too.

Doug Lubey of Louisiana www.DougLubey.com

search reference tags: sql 2005 stored procedure into temp table

openquery with stored procedure and variables 2005

openquery with variables

execute stored procedure into temp table

UPDATE: THIS WILL NOT WORK WTIH TEMP TABLES so I had to result to manually creating the temp table.

BUMMER NOTICE: this will not work with TEMP TABLES http://www.sommarskog.se/share_data.html#OPENQUERY

reference: The next thing is to define LOCALSERVER. It may look like a keyword in the example, but it is in fact only a name. This is how you do it:

sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servernameTo create a linked server, you must have the permission ALTER ANY SERVER, or be a member of any of the fixed server roles sysadmin or setupadmin.

OPENQUERY opens a new connection to SQL Server. This has some implications:

The procedure that you call with OPENQUERY cannot refer temp tables created in the current connection. The new connection has its own default database (defined with sp_addlinkedserver, default is master), so all object specification must include a database name. If you have an open transaction and are holding locks when you call OPENQUERY, the called procedure can not access what you lock. That is, if you are not careful you will block yourself. Connecting is not for free, so there is a performance penalty.

Answer 13

How would you use common table expression? Thanks Ray Akkanson