Mark B. – tech

SQL Server : OPENQUERY

OPENQUERY :: executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Syntax

OPENQUERY ( linked_server , query‘ )

link :

http://msdn.microsoft.com/en-us/library/ms188427.aspx

Examples

This example assumes that an Oracle database alias called ORCLDB has been created.
EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO
A. Executing a SELECT pass-through query

The following example creates a linked server named OracleSvr against an Oracle database by using the Microsoft OLE DB Provider for Oracle. Then, this example uses a pass-through SELECT query against this linked server.

SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles’)


B. Executing an UPDATE pass-through query

The following example uses a pass-through UPDATE query against the linked server created in example A.

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

C. Executing an INSERT pass-through query

The following example uses a pass-through INSERT query against the linked server created in example A.

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');


D. Executing a DELETE pass-through query

The following example uses a pass-through DELETE query to delete the row inserted in example C.

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
Advertisements
This entry was published on May 30, 2011 at 9:38 am and is filed under IT - Tutorial. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

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

%d bloggers like this: