Monday, December 22, 2008

How to query to linked server by using SQL Server

Assume that you have a linked server MyERP which connects to an Oracle database.
To be able to access data in this database in SQL Server, you can use OPENQUERY
Syntax for OPENQUERY:
OPENQUERY(MyERP,'Your query')

Some examples:

1. Select all employees in table tblEmployee:
SELECT *
FROM OPENQUERY(MyERP,'SELECT * FROM tblEmployee');

2. Update tblEmployee
UPDATE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')
SET EmployeeName = 'Barak Obama'

3. Delete tblEmployee:
DELETE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')

4. Insert to tblEmployee:
INSERT OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee')
VALUES('Barak Obama')

For more information, click here

No comments:

Post a Comment

Oracle Query