Export Stored Procedure Result with OPENQUERY

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

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


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s