In SQL Server, you can use OPENQUERY 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 INSERT pass-through query.

Example

Let’s say we created a table on the linked server called Cats, in a database called Pets.

Something like this:

EXEC ('
    USE  [Pets];
    CREATE TABLE [dbo].[Cats](
        [CatId] [int] IDENTITY(1,1) NOT NULL,
        [CatName] [varchar](60) NULL
    ) ON [PRIMARY];
') AT Homer;

And now we want to insert data using an INSERT pass-through query.

We can do it like this:

INSERT OPENQUERY (
    Homer, 
    'SELECT CatName FROM Pets.dbo.Cats'
    )
VALUES 
    ( 'FluffDupp' ),
    ( 'Scratch' ),
    ( 'Long Tail' );

In this case, the linked server is called Homer, and we are inserting three values into the Pets.dbo.Cats table.

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 |
| 2       | Scratch   |
| 3       | Long Tail |
+---------+-----------+

Note that OPENQUERY does not accept variables for its arguments.