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
creating a new table from a query is very easy:
SELECT name, database_id into NewTable
FROM sys.databases;
go
select * from NewTable;
go
drop table newTable;
go
It is inserting results from one table to another table.
OK I’ve given the simplest example but it works just as well for complicated queries with joins, where, group by, having etc.
In fact we use this method every night to remake tables with 70 000 lines that we use for statistics which are based on a view using 5 tables and reasonably complicated conditions and totals: the users can query the table in less than five seconds, compared to about two minutes for the view.
Your proposed solution has nothing to do with the question. The question was inserting results from a stored procedure into a table. Nowhere did you address that.
Hi,
I tried this example.
Why we use Sql server native client, and How does it works here?
Hi,
I tried this example.
Why we use Sqlserver native client in OPENROWSET() , and how does it work here ?
Please explain..
If SP return more than one result set than??
if SP return more than one result set then, the result will be the result of first select statement.
try below code:
CREATE PROCEDURE GetDbNames
AS
SELECT [name] from sys.databases
SELECT [name],[database_id] from sys.databases
GO
EXEC GetDbNames
SELECT * INTO #Test1 FROM OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames’)
SELECT * FROM #Test1
DROP TABLE #Test1
Now, the temp table have only the result of “SELECT [name] from sys.databases”
If SP return more than one result set then,
the result in temp table will be the result of first select statement in sp.
try below query:
CREATE PROCEDURE GetDbNames
AS
SELECT [name] from sys.databases
SELECT [name],[database_id] from sys.databases
GO
EXEC GetDbNames
SELECT * INTO #Test1 FROM OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames’)
SELECT * FROM #Test1
DROP TABLE #Test1
Hi Mukesh,
What is the best way to store both result set return by SP in two different table.
we prefer to use the first method since we already know the number of columns beforehand.
what if we have some 100 columns?Can you go and create table? If you use second method then it will directly creates table. I will prefer second method.
depends on situation, you need to choose.
what if we are supplying some parameters .. ??
As we are not a database admins in our project. Whenever we created the SP we are the admins (during development) and SP executing successfully and data also inserted , but now the project in the live and we can’t use admin account for SP execution. Now DBA team is requesting us to change the SP to insert the EXCEL data to database table.
I have given these queries to DBA team to execute:
sp_configure ‘Show Advanced Options’, 1
GO
RECONFIGURE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO
but they are asking why to execute these, why can’t you change the SP.
Please help me on this?
Hi,
how to use OPENROWSET with EXEC sp along with their parameters?
Thanks
Best Regards
It can be done like this :
OPENROWSET(‘SQLNCLI’,’Server=.;Trusted_Connection=yes;’,’EXEC GetDbNames @job_name = N”jobname”’)
Hi Karthik,
Here you have entered hardcoded value in the parameter but if I want to pass dyanamic parameter then. Because I am trying to pass dyanamic paramenter using variable but it is giving me error “Incorrect Syntax near ‘+’. Expecting ‘)’.”
Please help
Thanks,
Mangesh
My stored procedure need to create temporary tables first. I used the second method to avoid dealing with a lot un-useful.columns. The select into statement came back error saying the temporary table name as “invalid object name”.
I believe this method is brilliant for everything except when the stored procedure is using temporary tables. I am trying to use the results of my dynamic pivot stored procedure with the above code and get the following error.
The metadata could not be determined because statement ‘INSERT INTO #Foo(Col1, Col2, ColNPlusOne)
SELECT Col1 ‘ in procedure ‘sp_DynamicSurveyPivotData’ uses a temp table.
Think this is just a limitation of Sql Server 2012.
IMHO, you should never use select/into with tempdb as this causes concurrency issues. Always use create table and then insert your results. Your locking of the system tables in tempdb will be much shorter than a select/into which has to wait for entire query to process before it can release the locks it holds.
The INSERT will also take column names, so if new Docolumns are added, it won’t break the code. E.g.,
INSERT INTO #MyTable
(MyColumn1, MyColumn2)
Exec MyProc
But this can cause performance issues (in my experience).
To answer your question about why this is needed, I like to use inserting into tablea this way for code re-usability.
SELECT * INTO #TestTableT FROM OPENROWSET(‘SQLNCLI’, ‘Server=NEHA-PC;Trusted_Connection=yes;’,
‘exec OCFSERVER.dbo.Performance ”2014/06/01”,”2014/06/30”,868440’)
this is not working
error:
Msg 208, Level 16, State 1, Procedure Performance, Line 16
Invalid object name ‘#TempPerformance’.
#TempPerformance is my temp table in OCFSERVER.dbo.Performance..
please tell me how to work..
Thank if any one reply
how to pass parameters to the stored proc in this case
I have the same question as UnKnMsTr… I have tried the following:
SELECT * INTO #DistinctRels FROM OPENROWSET(‘SQLNCLI’, ‘Server=ATO-TAS;Trusted_Connection=yes;’,
‘EXEC sp_get_all_distinct_relationship_information Govern’)
Where “Govern” is the parameter I need to pass to my SP. I get the following error:
Could not find stored procedure ‘sp_get_all_distinct_relationship_information’.
Yet, when I run the SP by itself:
sp_get_all_distinct_relationship_information ‘Govern’, it works fine
create table #DB_Size_With_All_Tables
(
name varchar(max),
rows int,
reserved varchar(max),
data varchar(max),
index_size varchar(max),
unused varchar(max)
)
declare @tbl_nm varchar(max)
declare @script varchar(max)
declare curr cursor local for select name from sys.tables
OPEN curr
FETCH NEXT FROM curr INTO @tbl_nm
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @script = ‘insert into #DB_Size_With_All_Tables exec sp_spaceused ‘+@tbl_nm
EXECUTE (@script)
FETCH NEXT FROM curr INTO @tbl_nm
END
close curr
deallocate curr
select * from #DB_Size_With_All_Tables
drop table #DB_Size_With_All_Tables
Shivakumar – Thanks for sharing it.
it is going to default database?
I want to put this dynamic pivot query in Table-valued Function.
can you sir please help me.
Declare @cols NVARCHAR(MAX);
Declare @query AS NVARCHAR(MAX);
Set @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(ProjectProperties.PropertyId)
FROM ProjectProperties
FOR XML PATH(”) ,TYPE
).value(‘.’, ‘NVARCHAR(MAX)’),
1, 1, ”)
set @query = ‘SELECT ProjectId, ‘ + @cols + ‘ from
(
select ProjectId, PropertyId, PropertyValue
from ProjectProperties
) x
pivot
(
max(PropertyValue)
for PropertyId in (‘ + @cols + ‘)
) p ‘
execute(@query)
What exactly is the output you want?
Actually i have data in row so i have converted it in columns by this type of query,
Now i want add it in table valued function so i can join this table in my procedure query and get as my desire output with procedure query. but column is not fix and this is dynamic query due to that i can not add this is in table valued function, is it possible to add this in table valued function?
if bellow query for create function is possible then also fine, i’ll add my pivot query in procedure and call this procure from function.
CREATE FUNCTION Test()
RETURNS TABLE AS
RETURN
(
exec sp_test
)