SQL OpenQuery Samples

The OPENQUERY command is used to initiate an ad-hoc distributed query using a linked-server.
It is initiated by specifying OPENQUERY as the table name in the from clause.
Essentially, it opens a linked server, then executes a query as if executing from that server.

While executing queries directly and receiving data directly in this way is not bad, there are implications when trying to join the results of OPENQUERY with a local table.
Typically joining remote tables with local tables across the network is not the most efficient method of querying.
In some cases it may be better to subscribe to a remote table by importing it locally, then joining it locally.

Examples:

select * from OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE id = 101’)

UPDATE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE id = 101’)
SET name = ‘ADifferentName’;

INSERT OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles’)
VALUES (‘NewTitle’);
INSERT OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles’)
SELECT name FROM DBO.LOCALSQLSERVERTABLETILES;

DELETE OPENQUERY (OracleSvr, ‘SELECT name FROM joe.titles WHERE name = ”NewTitle”’);

MSDN OPENQUERY Reference and SqlServerPlanet OpenQuery Reference

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