Insert Stored Procedure Results Into Table

Since stored procedures are generally the most efficient way to call and retrieve data, it’s natural to want to encapsulate logic in them and call them when needed. With this use also comes the need to retrieve the data they return. When you already know the datatypes and columns being returned from the procedure, this is a relatively easy task. Let’s start by looking at an example call.

-- Create a sample stored procedure
CREATE PROCEDURE GetList
AS
BEGIN
SELECT
ListName = 'MyList'
,ListNumber = 1
END
GO

-- this table will house our results
CREATE TABLE #List
(
ListName VARCHAR(25),
ListNumber INT
)

-- finally, execute and insert into our table
INSERT INTO #List
(
ListName,
ListNumber
)
EXEC dbo.GetList

That was relatively easy, however dynamically retrieving the result set from a stored procedure without knowing the columns and datatypes returned in advance has historically proven to be a difficult task. Fortunately with the advent of OPENROWSET, our life as developers has become much simpler. Using openrowset is very simple when we do not have procedures that take parameters, However when parameters become involved the game changes, however there is a workaround.

In using OPENROWSET we need to turn on the ability to run distributed queries on your server. To enable this is simple, given you have the appropriate permissions. It’s a simple process when we do not have to pass in parameters.

sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE

Using OPENROWSET for a Stored Procedure with No Parameters

To get the result set is simple, simply call OPENROWSET passing the parameter of driver name, connection string and command text and we’re finished.

SELECT *
INTO #Jobs
FROM OPENROWSET('SQLNCLI', 'server=SERVERNAMEINSTANCENAME;trusted_connection=yes',
'set fmtonly off exec msdb.dbo.sp_help_job')

Using OPENROWSET for a Stored Procedure with Parameters

A whole different scenario presents itself when we need to use the above method to call a stored procedure and pass dynamic parameters. The issue is that you cannot concatenate a string within OPENROWSET. Here is how we do it:

First we need to create a procedure that wraps OPENROWSET. Make sure to replace the server and instance with your servernameinstancename values.

CREATE PROCEDURE ProcToTable
(
    @TempTableName nvarchar(100)
    ,@SPAndParameters nvarchar(MAX)
)
AS
BEGIN
    DECLARE @Driver nvarchar(20)
    DECLARE @ConnectionString nvarchar(200)
    DECLARE @SQL nvarchar(MAX)
    DECLARE @RowsetSQL nvarchar(MAX)

    SET @Driver = '''' + 'SQLNCLI' + ''''
    SET @ConnectionString = '''' +'server=SERVERNAMEINSTANCENAME;trusted_connection=yes' + ''''
    SET @RowsetSQL = '''' +'SET FMTONLY OFF ' + '' + @SPAndParameters + '' + ''''
    SET @SQL = 'INSERT INTO ##' + @TempTableName + ' SELECT * FROM OPENROWSET('

    SET @SQL = @SQL + @Driver + ',' + @ConnectionString + ',' + @RowsetSQL + ')'
    EXEC (@SQL)
END

-- Now that the procedure is created, let's run our query
SELECT TOP 0 *
INTO #holdingtable
FROM OPENROWSET('SQLNCLI','server=SERVERNAMEINSTANCENAME;trusted_connection=yes','SET FMTONLY OFF exec sp_who 1')
-- set the parameters of the procedure
SET @SPID = 1
SET @SPWithParams = 'exec sp_who ' + @SPID
-- execute the procedure
EXEC ProcToTable 'holdingtable', @SPWithParams
-- view the results
SELECT * FROM #holdingtable

This procedure we created, uses a temp table to insert the result of our parameterized stored procedure query. So with that in mind, we need to first create the temp table prior to executing the procedure. We need to execute the procedure using default parameters and using a SELECT TOP 0 *, in order to create the structure of our table.

USING OPENQUERY

The other option is to use OPENQUERY. It is basically the same as OPENROWSET, however you can use this option if for some reason you can’t get OPENROWSET to work. There is an additional setup using this. You have to create a linked server connection to your own local server. Click here for instructions to create a local linked server

-- Enable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

SELECT *
INTO dbo.List
FROM OPENQUERY(me, 'set fmtonly off exec dbo.GetList')
ORDER BY 1

17 comments
Rohit Pandit 28 Aug 2014 at 11:26 am

Thanks a lot. Really helped

Pathan 16 May 2012 at 11:08 am

Hi,
i have created following storedProc
How can i Execute that and how can i insert the values into table
ALTER proc insert_details(@UserId int ,@UserName varchar(50),@MobileNo varchar(50),@UserRole int)
AS
BEGIN
INSERT INTO Registation
(UserId,UserName,MobileNo,UserRole)
values(@UserId ,@UserName ,@MobileNo ,@UserRole )
RETURN 0
END

Thanks a lot in advance

URVISH SUTHAR 31 Mar 2012 at 4:04 am

DECLARE @EmpGroup INT =3 ,
@IsActive BIT=1

DECLARE @tblEmpMaster AS TABLE
(EmpCode VARCHAR(20),EmpName VARCHAR(50),EmpAddress VARCHAR(500))

INSERT INTO @tblEmpMaster EXECUTE SPGetEmpList @EmpGroup,@IsActive

SELECT * FROM @tblEmpMaster

joy 21 Jan 2012 at 1:58 pm

how make a procedure to insert the values where one column is the foregin key of the primary key in a single table
like

create table test1(id int identity constraint mno1 primary key ,name varchar(100), sno int constraint constraint_test1_id_fk references test1(id))

farid 11 Jan 2012 at 9:41 am

How produce present store procedure from tables?

rmotiani 22 Nov 2011 at 9:09 am

How to pass a table variable in OpenRowSet?

Vince 18 Sep 2011 at 6:52 am

“Retrieving the result set from a stored procedure has historically proven to be a difficult task.”

Uhh… why not just:

INSERT INTO #TABLE
EXEC sp_name

or if you need specific columns
INSERT INTO #TABLE
(ColumnName1, ColumnName2)
EXEC sp_name

Am I missing the true point of this post? It doesn’t seem very a very difficult task to me.

Derek Dieter 19 Sep 2011 at 10:01 pm

Hi Vince,

The problem I’ve always faced is not knowing what the column names or data types is of the procedure you are executing in advance. These are needed in order to create the temp table definition. The ultimate solution is to create the temp table dynamically based on the result set of the procedure at execution time. That’s the scenario this article addresses. Does that make sense or am I missing something also?

Sandeep 12 May 2011 at 3:19 pm

Hi,

Everything works fine. But I am facing problem when Sproc outputs a XML result set.

For Example,
Output of
EXEC DB.schema.ProcName

Result set is

4F7135AF-03C2-4166-9645-A81EED3656AC
false
0

{NONE}

However when i tried

SELECT *
FROM OPENQUERY([LOCALSERVER], ‘EXEC DB.schema.ProcName’)

Result set is something like this below. which is in binary or image format.

0x44065400690063006B006500740044085400690063006B006500740049004400440E53006F00750072006300650045006E00640070006F0069006E0074004413440065007300740069006E006100740069006F006E0045006E00640070006F0069006E0074004409500072006F0063006500730073004900440044114F0072006900670069006E0061006C004D00650073007300610067006500490040000040074727565810C010D410310E4040000050066616C7365810D010E410310E4040000050066616C7365810E010F410310E4040000010030810F0110410111410112410310E404000006007B4E4F4E457D8112811181108101

How do I proceed from here.?

Anonymous 14 Mar 2011 at 2:58 am

koloko hmer

Stephen 07 Oct 2010 at 11:20 am

So, how do I take a stored procedure that has multiple selects and take the results of those selects and put them into different tables? Any help would be greatly appreciated. Thanks!

Derek Dieter 13 Oct 2010 at 10:42 pm

Hi Stephen,

You can’t do that unfortunately. While this article does show how to take the result of a stored procedure and capture the result, it’s really a frowned upon way to do things. Ultimately, procedures should not return multiple result sets. When creating a procedure, the goal should be to make it as atomic as possible. Meaning it should only serve one purpose.

unruledboy 05 Jul 2010 at 6:53 pm

how to pass a temp table from one sp to another sp without the table name and exec sql method?

I mean can we pass the temp table like:

CREATE PROCEDURE TestTablePara2
@TempTable TABLE
AS

Coco 11 Apr 2010 at 7:20 pm

Sick man! I have to document a full db of sprocs (fun) and this’ll do the trick nicely for getting some things that’ll me go

Derek D. 11 Apr 2010 at 8:11 pm

Great! This is pretty useful. It would also be nice if SQL Server would officially support the insertion of a result set into a table without having to do workarounds.

Create Local Linked Server for OPENQUERY 18 Jun 2009 at 10:32 pm

[…] For that example, follow this link: (Insert Results of Stored Procedure Into Table) […]

Featured Articles

 Site Author

  • Thanks for visiting!