Populate table from Stored Procedure resultset in SQL Server

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

BTW, I have few more article where I have used OPENROWSET, click on the link to see more examples of OPENROWSET.

http://blog.extreme-advice.com/2011/07/05/openrowset-and-openquery-in-sql-server-20052008/

http://blog.extreme-advice.com/2011/07/15/filestream-in-sql-server-2008/

http://blog.extreme-advice.com/2011/07/06/msg-7301-cannot-obtain-the-required-interface-iid_idbschemarowset-from-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-linktoaceess/

http://blog.extreme-advice.com/2009/08/10/insert-and-update-image-field-in-sql-server-20082005/

http://blog.extreme-advice.com/2009/04/25/load-relational-xml-data-in-sql-server-2005/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations