Export Stored Procedure Result with OPENQUERY


  
-- EXEC sp_serveroption 'myserver\Instancename', 'DATA ACCESS', TRUE

SELECT  *
INTO     mytmpTable
FROM    OPENQUERY("myserver\Instancename", 'EXEC mydbname.[dbo].[usp_name_here] 2') -- with parameter value 2
 
select * from mytmpTable

drop table mytmpTable


 

You can use INSERT INTO atable EXECUTE sp syntax to put result into an existing table.

  
 
if object_id('datatemp','U') is not null 
drop table datatemp

--create a temp table base Stored procedure resultset
create table datatemp (id int, col varchar(50))


---insert result from stored procedure
INSERT INTO  datatemp(id, col)
EXECUTE yourspname @parameter1

--clean up
Drop table datatemp

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s