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 |
Comments
Post a Comment