SQL SERVER – How to INSERT data from Stored Procedure to Table – 2 Different Methods

Here is a very common question which I keep on receiving on my facebook page as well on twitter.

“How do I insert the results of the stored procedure in my table?”

This question has two fold answers – 1) When the table is already created and 2) When the table is to be created run time. In this blog post we will explore both the scenarios together.

However, first let us create a stored procedure which we will use for our example.

CREATE PROCEDURE GetDBNames
AS
SELECT
name, database_id
FROM sys.databases
GO

We can execute this stored procedure using the following script.

EXEC GetDBNames

Now let us see two different scenarios where we will insert the data of the stored procedure directly into the table.

1) Schema Known – Table Created Beforehand

If we know the schema of the stored procedure resultset we can build a table beforehand and execute following code.

CREATE TABLE #TestTable ([name] NVARCHAR(256), [database_ID] INT);
INSERT INTO #TestTable
EXEC GetDBNames
-- Select Table
SELECT *
FROM #TestTable;

The disadvantage of this code is that if due to any reason the stored procedure returns more or less columns it will throw an error.

2) Unknown Schema – Table Created at Runtime

There are cases when we do know the resultset of the stored procedure and we want to populate the table based of it. We can execute following code.

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.GetDBNames')
-- Select Table
SELECT *
FROM #TestTableT;

The disadvantage of this code is that it bit complicated but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

I will be interested to know which of the above method do you use in your projects? and why?

Reference:  Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Blogging Best Practices – Checklist for Building Successful Blog – Part 6
Next Post
SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL

Related Posts

57 Comments. Leave new

  • I will be using the second method, as i have to get records from many databases and the table structure is partially known.

    Reply
  • I get the desired result set from the temp table, but SSMS throws an error stating “An INSERT EXEC statement cannot be nested.” (again it works and I can pull the data I need from temp table, then drop the temp table).

    Reply

Leave a Reply

Menu