How to Insert the Results of a Stored Procedure into a Temporary Table in SQL Server

Data Tutorial

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 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
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 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;
GO

This 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.