In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an DELETE pass-through query.
Example
Suppose the linked server called Homer has a table in the Pets database called Cats with the following data.
SELECT * FROM OPENQUERY (
Homer,
'SELECT * FROM Pets.dbo.Cats'
);Result:
+---------+------------+ | CatId | CatName | |---------+------------| | 1 | FluffDupp | | 2 | Scratch | | 3 | Short Tail | +---------+------------+
And let’s say we want to delete one of the cats from the database.
We could do that using a DELETE pass-through query against the linked server, like this.
DELETE OPENQUERY (
Homer,
'SELECT CatName FROM Pets.dbo.Cats WHERE CatId = 2'
);Result:
(1 row affected)
Check the Results
We can now run a SELECT pass-through query to see the results.
SELECT * FROM OPENQUERY (
Homer,
'SELECT * FROM Pets.dbo.Cats'
);Result:
+---------+------------+ | CatId | CatName | |---------+------------| | 1 | FluffDupp | | 3 | Short Tail | +---------+------------+
As expected, the second row was deleted, and there are now only two row remaining.
One thing to note is that OPENQUERY does not accept variables for its arguments.