Create SQL Server linked server to PostgreSQL database

1. Download ODBC Driver at this address http://www.postgresql.org/ftp/odbc/versions/msi/

Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)

2. Install ODBC Driver for PostgreSQL

3. Create connection to PostgreSQL database with ODBC Driver

4. Now, it’s time to create linked server to PostgreSQL database:

You can create with GUI

Or can create with scripts

EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';

GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

Finally, you can query PostgreSQL database inside SQL Server

5. Verify the linked server

Create linked server to Access 2007 accdb file

EXEC master.dbo.sp_addlinkedserver @server = N'AccessLinkedServerName', @srvproduct=N'Access', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\Projects\MyAccessDB.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AccessLinkedServerName', @locallogin = NULL , @useself = N'False'

Create mutiples linked servers

When you need to work with multiple databases from Oracle, for example: 100 databases with name like that DB01, DB02, ..., DB100. All of them use the same user name and password to query data such as scott/tiger. Your job is to connect them from SQL Server. First, you need to create 100 linked servers. It takes much time to create 100 linked servers like that by using create linked server screen.
You can do this by this script.

DECLARE @LINK_NAME VARCHAR(10)
DECLARE @SERVER_NAME VARCHAR(50)
DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < 101
BEGIN
SET @LINK_NAME = 'DB' + RIGHT('00' + CAST(@COUNT AS VARCHAR),2);
SET @SERVER_NAME = @LINK_NAME + '{Some name}';
EXEC master.dbo.sp_addlinkedserver @server = @LINK_NAME
, @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=@SERVER_NAME;
EXEC sp_addlinkedsrvlogin @LINK_NAME, 'false', 'domain\user', 'scott', 'tiger';

SET @COUNT = @COUNT + 1;
END

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