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.
CREATE PROC uspGetSaleOrderHeader
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