How to insert the results of a stored procedure into a temporary table in SQL server
Posted by: AJ Welch
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
GOIdeally, 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 INSERT, DELETE, or 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.
Before using 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
GONow 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 PROVIDER_NAME and 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;
GOThis function can then be used roughly in the same manner as desired above using OPENROWSET:
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.