There are several ways to access and manipulate data across a database link. Each has various advantages and disadvantages:
Use SQL that directly references the database objects across the link. This is the easiest way to access remote objects. Depending on the size of the objects involved and the network between the two servers, however, it may be the slowest option from a performance point of view.
Use the OPENQUERY function in SQL statements. This function allows DML to execute on the remote server. This method performs much better than directly querying the linked server, but is not the easiest method to use from a syntax standpoint.
Execute remote stored procedures. For applications that are performing the same database operations as programs, this is the best method for both performance and code reusability.
To access linked objects directly, use a four-part naming scheme to name the object, with these parts separated by periods:
Owner of the object
For example, suppose you want to perform a SELECT statement against a table called products, owned by dbo in the database InformIT across the link named STAGING. This is how the SQL query might look:
SELECT product_id, product_name FROM STAGING.InformIT.dbo.products
Tables can be joined on either side of a database link, and various conditions can be placed on them. Let's assume that the remote product table in the previous example had to be joined locally to a local authors table with a product_id foreign key. The query might look like this:
SELECT a.product_id, a.product_name, b.author_name FROM STAGING.InformIT.dbo.products a, authors b WHERE a.product_id = b.product_id
Updates work in the same manner; just use the extended naming method to reference remote objects. Suppose that the local database has a table named imprints and the description column has been updated recently. We want that change to be moved to the linked server STAGING that also has the imprint table:
UPDATE STAGING.InformIT.dbo.imprints SET description = a.description FROM imprints a, STAGING.InformIT.dbo.imprints b WHERE a.imprint_id = b.imprint_id
Deletes also work this way.
There are some exceptions to this scheme:
Text fields cannot currently be updated across a database link. Updates have to be "faked" by doing a DELETE and then an INSERT for the text fields.
Text field pointers also don't work across links.