Remote Insert

Introduction

The Remote Insert operator inserts the rows it receives from its child operator in a table or view on a remote server. It reads rows from its child, one by one, and sends them to the remote server for inserting. If an error is returned from the remote server, then the transaction is aborted and the same error is raised locally.

Cases where Remote Insert are used are, based on my observations so far, are:

Using an INSERT statement with a remote table or view as the target.
Using an INSERT statement with an OPENDATASOURCE expression as the target.
Using an INSERT statement with an OPENQUERY expression as the target.
Using an INSERT statement with an OPENROWSET expression as the target.

If you find other cases where the Remote Insert operator is used, please let me know!

Visual appearance in execution plans

Depending on the tool being used, a Remote Insert operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Remote Insert operator is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.

Send insert query

The Send insert query action sends the data to be inserted in the Remote Object, in the form of a single-row insert statement, to the remote server where the data has to be inserted (as stored in the Remote Source property). The exact usage of the relevant properties varies by use case.

For an INSERT into a table or view, the Remote Object property holds the object name (using three-part naming: database, schema, and table or view), whereas the server name is stored in the Remote Source property.

For an insert into an OPENDATASET expression, the Remote Object property holds the object name (using three-part naming: database, schema, and table or view). The Remote Source is set to “<UNKNOWN>”. I assume that the actual information (provider name and connection string) is stored in the internal execution plan, but not included in the export to XML.

For an insert into an OPENQUERY expression, the Remote Object is the query passed into the OPENQUERY expression, whereas the server name is stored in the Remote Source property.

For an insert into an OPENROWSET expression, the Remote Object is either the object name (using three-part naming: database, schema, and table or view), or the query passed into the OPENROWSET expression. The Remote Source is set to “<UNKNOWN>”. I assume that the actual information (provider name, plus either connection string or data source, user id, and password) is stored in the internal execution plan, but not included in the export to XML.

In all cases, the Predicate expression lists a series of assignments that specify which column from the input has to be inserted in which column in the target object.

Operator properties

The properties below are specific to the Remote Insert operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Remote Insert operator are marked with a *.

Property name	Description
Output List *	In all cases so far where I have looked at the Remote Insert operator, the Output List property was empty in the XML and not shown in the graphical execution plan. The operator still returns a row to its parent every time it is called, but it is an empty (zero columns) row. I don’t know whether this is a limitation of the operator, or whether I just haven't been able to construct the right test case yet.
Predicate	Maps columns from the input stream to the columns in the Remote Object.
Remote Object	The object (table or view name) on the remote server that the rows have to be inserted into, using three-part naming (database name, schema name, and table or view name). When Remote Insert is used with an OPENROWSET or OPENQUERY target, the Remote Object property can also be the text of a query, that has to follow the rules for updateable views.
Remote Source	The name of the server where the rows have to be inserted in the Remote Object. When Remote Insert is used to insert into an OPENDATASET or OPENROWSET expression, this property shows “”. I assume that the actual information (provider name, plus either connection strong or data source, user id, and password) is stored somewhere in the actual internal execution plan, but not included in the XML representation.

Implicit properties

This table below lists the behavior of the implicit properties for the Remote Insert operator.

Property name	Description
Batch Mode enabled	The Remote Insert operator supports row mode execution only.
Blocking	The Remote Insert operator is non-blocking.
Memory requirement	The Remote Insert operator does not have any special memory requirement.
Order-preserving	I assume that the Remote Insert operator is fully order-preserving. However, since I have not been able to construct execution plans where the Remote Insert operator returns actual data to its parent, so I have not yet been able to verify this assumption.
Parallelism aware	The Remote Insert operator can only be used in serial execution plans.
Segment aware	The Remote Insert operator is not segment aware.