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
SELECT name, database_id
We can execute this stored procedure using the following script.
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
-- Select Table
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;',
-- Select Table
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
sp_configure 'Ad Hoc Distributed Queries', 1
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)
I will be using the second method, as i have to get records from many databases and the table structure is partially known.
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).
I want to use Method 1, and it works when you run a stand alone query. but when I wrap this code inside another SP I get “An INSERT EXEC statement cannot be nested.”
How can I avoid this error or is there any way around for this error message?