Loading stored procedure results into tables

There are multiple methods that can be used to import stored procedures results into tables.

OPENROWSET AND OPENQUERY methods require that stored procedures don’t have;

  1. temp tables inside
  2. Don’t return duplicate columns

There is no need for a linked server, but you would need to get the connection string right. You need to specify the full path to the stored procedure including the database name and the stored procedure owner.

METHOD–1 Using OPENROWSET

This is one time step to configure database server.

--one time step
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENROWSET') IS NOT NULL DROP TABLE #STG_OPENROWSET
SELECT * INTO #STG_OPENROWSET FROM OPENROWSET('SQLNCLI', 
'Server=(local);Trusted_Connection=yes;',
'EXEC DatabaseName.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020')
SELECT * FROM #STG_OPENROWSET
DROP TABLE #STG_OPENROWSET

METHOD–2 Using OPENQUERY.

This is one time step to configure database server.

--one time step
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE

You can run your stored procedures that will load data into tables;

IF OBJECT_ID('tempdb..#STG_OPENQUERY') IS NOT NULL DROP TABLE #STG_OPENQUERY
SELECT  * INTO    #STG_OPENQUERY
FROM    OPENQUERY(DatabaseServerName, 
'EXEC	Database.[dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020');
SELECT * FROM #STG_OPENQUERY
DROP TABLE #STG_OPENQUERY

If stored procedures are using temp tables or returning duplicate columns, you will get these errors using Method-1 and Method-2;

invalid object #tablename.

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET”

OPENROWSET are not allowed in Azure SQL. They are allowed in a VM that is connected to Azure.

METHOD–3  Manually create temp tables

There is no database configuration required.

You can run your stored procedures that will load data into tables;

--the proce is using temp tables so this is work around
IF OBJECT_ID('tempdb..#STG_TempTable') IS NOT NULL DROP TABLE #STG_TempTable
CREATE TABLE #STG_TempTable
(
	ID int,
	[name] nvarchar(255),
	shortName nvarchar(25),
)
INSERT #STG_TempTable 
EXEC [dbo].[StoredProcedureName] @param1 = 1, @param2 = 2020
SELECT * FROM #STG_TempTable

By using this method, your database administrator will be happy because you are not making any security related changes at database server level.

References

https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table

FavoriteLoadingAdd to favorites
Spread the love

Author: Shahzad Khan

Software developer / Architect