Linked Server SQL Syntax for SQL Server

Here they are some examples how to use linked server to execute SELECT, INSERT, UPDATE and DELETE statements. Run these statements in either Query Analyzer for SQL Server 7 / 2000 or in New Query field for SQL Server 2005 / 2008. These examples assume following conditions:

1. "MYLINKEDSERVER" as the name for a linked server that connect to PostgresSQL Database using pgsqlODBC.
2. BOOKS is a table in a schema named LIBRARY, and AUTHOR is a column in BOOKS table.

SELECT Statement

SELECT AUTHOR FROM MYLINKEDSERVER.LIBRARY.BOOKS

OR

SELECT * FROM OPENQUERY
(MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')

INSERT Statement

INSERT INTO MYLINKEDSERVER.LIBRARY.BOOKS (AUTHOR) VALUES ('Shaun Walker')

OR

INSERT INTO OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
VALUES ('Shaun Walker')

UPDATE Statement

UPDATE MYLINKEDSERVER.LIBRARY.BOOKS SET AUTHOR='Bill Gates' WHERE AUTHOR='Shaun
Walker'

OR

UPDATE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS') SET
AUTHOR='Bill Gates' WHERE AUTHOR='Shaun Walker'

DELETE Statement

DELETE MYLINKEDSERVER.LIBRARY.BOOKS WHERE AUTHOR='Shaun Walker'

OR

DELETE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
WHERE AUTHOR='Shaun Walker'

NOTE:

To perform DELETE or UPDATE operations, the target table must have a unique index. For more information, please refer to the section titled UPDATE and DELETE Requirements for OLE DB Providers in the SQL Server Books Online.