MS-SQL : OpenQuery

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

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name

Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.

Table Structure

create table OpenQury

(
      ID int,
      Name varchar(240),
      Amount decimal(21,3)
)

Query

SELECT * FROM OPENQUERY (LINKEDSERVERNAME, 'SELECT id,name,amount FROM coldstart.dbo.OpenQury ')

Fig 1

UPDATE OPENQUERY (LINKEDSERVERNAME, 'SELECT amount FROM coldstart.dbo.OpenQury where id = 41038306')

SET amount = 100000

Fig 2

INSERT OPENQUERY (LINKEDSERVERNAME, 'SELECT id,name,amount  FROM coldstart.dbo.OpenQury')

VALUES (41038311,'Jithesh',20000);

Fig 3

DELETE OPENQUERY (LINKEDSERVERNAME, 'SELECT id,name,amount  FROM coldstart.dbo.OpenQury WHERE ID = 41038311');

Fig 4