I was answering post in MSDN website today and found one question which was asking “How to populate table from stored procedure in SQL Server?”. This is quite a common question and I have answered it in Experts-Exchange.com forum so many times but today I felt to share it with my blog reader.
Actually there is no direct way to populate result of stored procedure into SQL Server table but yes, you can use OPENROWSET to achieve this task. Let us look at it practically.
USE AdventureWOrks2012 GO CREATE PROC uspGetSaleOrderHeader AS SELECT SalesOrderNumber, RevisionNumber, OrderDate, ShipDate, AccountNumber, CustomerID, TotalDue FROM Sales.SalesOrderHeader GO IF OBJECT_ID('tempdb..#TempSalesTable') IS NOT NULL DROP TABLE #TempSalesTable SELECT * INTO #TempSalesTable FROM OPENROWSET('SQLNCLI', 'Server=WIN-9H6QATRKY81\SQL2K12DEV;UID=sa;PWD=upgrade1;','EXEC AdventureWOrks2012..uspGetSaleOrderHeader') SELECT * FROM #TempSalesTable
If “Ad Hoc Distributed Queries” is disabled in your SQL Server, you will not be able to use OPENROWSET. You can enable “Ad Hoc Distributed Queries” with small TSQL Script, click here, for more details about this.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations