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.