How to Insert the Results of a Stored Procedure into a Temporary Table in SQL Server
In some cases with SQL Server, there may be an instance where you wish to take the resulting data from a stored procedure and insert it into a temporary table for use in another query. Determining how to accomplish this task can be somewhat difficult, so we'll briefly outline a couple options, depending on your specific needs and database configuration.
Before we examine the specific methods, let's create an example procedure. While not particularly useful, let's create the
BooksByPrimaryAuthor procedure, which accepts the
@PrimaryAuthor parameter and grabs records from our
books table where that
@PrimaryAuthor matches. The procedure generation statement might look like this:
CREATE PROC BooksByPrimaryAuthor @PrimaryAuthor nvarchar(100) AS BEGIN SELECT * FROM books WHERE primary_author = @PrimaryAuthor; END GO
Ideally, what we'd like to do is to is something like this, where we
SELECT the resulting data from our procedure and insert it into a temporary table:
SELECT * INTO #tmpSortedBooks FROM EXEC BooksByPrimaryAuthor 'Tolkien'
The problem is the above syntax is improper and will not work. We need a new method.
Using the OPENROWSET Statement
One possibility is to use the
OPENROWSET statement, which allows you to access remote data from an OLE DB source and can be executed directly from within another SQL statement.
OPENROWSET is a one-time connection and data retrieval method, so it should not be utilized for frequent connections (linking servers is preferable in that case).
OPENROWSET can be the target of any
UPDATE statement, which makes it ideal for our purposes of "executing" our stored procedure for us and extracting that data back out to our waiting temporary table.
OPENROWSET, it may be necessary to modify some configuration options, specifically by allowing ad hoc access. This can be configured using the following statements:
sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO
Now we can utilize
OPENROWSET, which has a particular syntax that must be adhered to:
OPENROWSET( <PROVIDER_NAME>, <DATA_SOURCE>, <OPTIONS> )
Thus, we may execute our stored procedure through
OPENROWSET and pass it to our temporary table like so:
SELECT * INTO #tmpSortedBooks FROM OPENROWSET( 'SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC BooksByPrimaryAuthor Tolkien' )
You may need to change the
DATA_SOURCE values for your own purposes.
Using a User-Defined Function
There are a few downsides to the
OPENROWSET method, namely that it requires ad hoc permissions/configuration as we saw above, and also
OPENROWSET is only capable of returning a single result set (if multiple sets are provided, only the first result set is returned).
Therefore, another method to perform this task is to effectively replace the stored procedure with a user-defined function instead.
From our example, that would look something like this:
CREATE FUNCTION BooksByPrimaryAuthor ( @PrimaryAuthor nvarchar(100) ) RETURNS TABLE AS RETURN SELECT * FROM books WHERE primary_author = @PrimaryAuthor; GO
This function can then be used roughly in the same manner as desired above using
SELECT * INTO #tmpSortedBooks FROM BooksByPrimaryAuthor('Tolkien')
In the event that you truly need a stored procedure, you can also wrap your function within a stored procedure as well.