How to INSERT data from Stored Procedure to Table ?

How to INSERT data from Stored Procedure to Table ?

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