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)
58 Comments. Leave new
Hi Jignesh i would like to do the same, i have the same piece of code that is generating a pivot table dynamically and would like to be able to have it as a view to link it to my main table. Did you find a solution to this?
Many thanks
Hi Pinal !
what about a non-trusted-connection.
what would be the syntax to fill in the credentials.
Many thanks
Hey Pini,
Have used your posts on several occasions and thought i would just thank you for work!
i want to pass dynamic parameter to this query. How ???????
declare @param1 varchar(255)
set @param1=’12456′
SELECT * INTO #TestTableT FROM OPENROWSET(‘SQLNCLI’, ‘Server=localhost;Trusted_Connection=yes;’,
‘EXEC tempdb.dbo.GetDBNames’)
— Select Table
SELECT *
FROM #TestTableT;
Pinal – once again you enlighten me. Many thanks!
Hi Can you please tell how can I insert data into two different tables from sp that return 2 result set
is there any way to store the output column name of stored procedure into a table.
exec sp_describe_first_result_set N’EXEC GetDBNames’
SELECT * INTO #TestTableT FROM OPENROWSET(‘SQLNCLI’, ‘Server=localhost;Trusted_Connection=yes;’,
‘EXEC tempdb.dbo.GetDBNames’)
— Select Table
SELECT *
FROM #TestTableT.
When i am supplying server name and procedure name with parameter of procedure i am getting error..
LOGIN FAILED FOR USER .
Please guide me
Thanks
Rabi
Trusted_Connection=yes will use Windows account. What is the user for with login is failing?
Do you have an answer for Shashi Kiran SinghOctober 16, 2015 1:41 pm?
I don’t see anyway in documentation.
Thanks for the question, I will look into it soon and will get back.
Hi Pinal,
I would like to use the first option but i want it to be more dynamic. Is there any option to select only two columns from the result set(exec sp) and insert them into temp table.
Hi Pinal,
I have some problem. When I insert into table exec proc param1,param2.. I have wrong result, if I execute only procedure, she give me good result. Why is this happening?
I have a question. In scenario number 2 above, what would the syntax look like for executing the stored proc when the proc accepts input parameters?
Hi Pinal, Your solution for Openrow set function work for me and I publish the table as output on my reporting dash board .
Thanks for sharing it Chandan. Appreciate it.
HI, what if the #Testtable resides in another server if the Testtable is already created?
The method 2 is not working for me with the following error. My stored procedure uses temporary tables
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘select * from #tempTrans’ in procedure ‘usp_XYZ’ uses a temp table.
I tried SET FMTONLY OFF as some people suggested but that didn’t make any difference. Any thought?
I am posting the code of my sample stored procedure if someone wants to reproduce the issue
create PROCEDURE usp_XYZ
AS
SET FMTONLY OFF
CREATE TABLE #tempTrans(Id int NOT NULL)
select * from #tempTrans
GO
SELECT * INTO #TestTableT FROM OPENROWSET(‘SQLNCLI’, ‘Server=localhost;Trusted_Connection=yes;’,
‘EXEC myDB.dbo.usp_XYZ’)
SELECT *
FROM #TestTableT;
GO
Is there a method for handling multiple data sets coming out of a stored procedure and getting them into multiple temp tables with T-SQL; without going near dot-Net & CLRs
Hi,
Is there anyway to insert into temporary table(first result set) if stored procedure(exec) returns multiple result set without modifying existing stored procedure?