Dec 9, 2014

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

“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

0 comments:

Post a Comment

Nam Le © 2014 - Designed by Templateism.com, Distributed By Templatelib