Insert results of a stored procedure into a temporary table
How do I do a
SELECT * INTO [temp table] FROM [stored procedure]? Not
FROM [Table] and without defining
Select all data from
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'
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.
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
Read more... Read less...
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'
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.
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE SELECT * INTO #tmpTable FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
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]')