Using Microsoft SQL Server to Update PostgreSQL Data

This article is a bit of a companion to our article on Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

In this article we shall demonstrate using Microsoft SQL Server 2005/2008 OPENQUERY AND OPENROWSET to add, delete and update data in PostgreSQL.

First we must start by saying there are a number of ways of copying data between databases. While OPENROWSET is not necessarily the fasted, in certain cases such as when you are wrapping this in a stored procedure, it is one of the most convenient ways of doing this.

Why on earth would you want to copy data back and forth between 2 servers and 2 disparate DBMS systems for that matter? We all would like to think we are an island and live in a world with one DBMS system, but we don't. There are many reasons for having multiple DBMS providers in an organization. Some are better for some things than others, some are more integrated in an environment -- (for example in a windows shop the SQL Server drivers are already loaded on all windows machines, but PostgreSQL provides the advantage of being able to run on more platforms such a FreeBSD/Unix/Linux box and with cheaper cost and more options for PL programming so is often better for a front-facing DMZ accessible database), and there are numerous other reasons that are too hard to itemize. The other question of why triggering from SQL Server instead of PostgreSQL is because its just a little easier from Microsoft SQL Server. The OPENROWSET and OPENQUERY logic that SQL Server provides is just simply better and easier to use than the dblink provided for PostgreSQL. Anyrate with that said lets move on with the show.

Although this example is focused on using PostgreSQL with Microsoft SQL Server, the same technique applies when copying retrieving updating data from other databases such as MySQL or Oracle or DB II.

OPEN what - what's the difference?

SQL Server provides two functions for doing cross server calls or pulling in files. One is called OPENQUERY and the other is OPENROWSET. The distinction is fairly simple.

OPENQUERY - needs a reference to an already estabilished Linked server. Linked server as we discussed in our prior article.
OPENROWSET - contains the connection string to the server/file so need not piggy back on a linked server setup. Since OPENROWSET is truly ad-hoc -- you need to have Ad-hoc Remote Queries enabled in your SQL Server surface area configuration. Or alternatively running the below commands as admin
```
sp_configure 'show advanced options', 1
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
	
```

The SQL Server 2005 examples below use the 64-bit ODBC driver for PostgreSQL and OLEDB Driver for ODBC. If you are using SQL Server 2008 and you have the PostgreSQL OLEDB driver installed, it appears that that is an option under installed providers though we haven't tested that out.

Doing a simple select of a PostgreSQL data

For a select from a PostgreSQL table or view or function, you can do this with a linked server, OPENQUERY or OPENROWSET. We shall demonstrate using OPENROWSET.

--SELECT example -- 64 bit ANSI driver
SELECT a.*
FROM OPENROWSET('MSDASQL', 
    'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;port=5432;database=pgdatabase;pwd=somepassword',
    'SELECT  fielda, fieldb, field2
            FROM sometable wHERE fielda LIKE ''test%'' ')
AS a;

--SELECT example -- 32-bit ANSI driver (if running 32-bit SQL SErver 2005/2008)
SELECT a.*
FROM OPENROWSET('MSDASQL', 
    'Driver=PostgreSQL ANSI;uid=pguser;Server=pghost;port=5432;database=pgdatabase;pwd=somepassword',
    'SELECT  fielda, fieldb, field2
            FROM sometable wHERE fielda LIKE ''test%'' ')
AS a;

Doing a delete from a PostgreSQL table

When doing a delete you want your OPENROWSET to return the records you want to delete. In this example we are deleting all the records like 'test%'.

--DELETE data
DELETE FROM
    OPENROWSET('MSDASQL', 
        'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;port=5432;database=pgdatabase;pwd=somepassword',
    'SELECT  fielda, fieldb, field2
            FROM sometable wHERE fielda LIKE ''test%'' ')

Doing an insert into a PostgreSQL table

Here is an example of doing an INSERT. The important thing to keep in mind here is when doing an insert have your input OPENROWSET return no records.

We want our function to return no data so it just provides the field structure and an empty result set to add to.

--INSERT data           
INSERT INTO OPENROWSET('MSDASQL', 'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;database=pgdatabase;pwd=somepassword',
    'SELECT  fielda, fieldb, field2
            FROM sometable WHERE fielda = ''-1'' ')
SELECT fielda, fieldb, fieldc
 FROM sqlservertable WHERE fielda LIKE 'test%'

Updates

Simple updates not bad. You only want to select the records you wish to update.

--UPDATE data
UPDATE OPENROWSET('MSDASQL', 'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;database=pgdatabase;pwd=somepassword',
    'SELECT  fielda, fieldb, field2
            FROM sometable WHERE fielda = ''test%'' ')
 SET fielda = 'my test'

Cross Updates doable but potentially very slow depending number of records you are pulling and updating. Things to keep in mind, include the primary key, only select the columns and rows you wish to update.

--Cross Update between PostgreSQL and SQL Server
-- Note here we alias the fields in our PostgreSQL so names don't conflict
-- with SQL Server field names
UPDATE OPENROWSET('MSDASQL', 'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;database=pgdatabase;pwd=somepassword',
    'SELECT  mykey As pgkey, myvalue As pgvalue
            FROM sometable WHERE fielda = ''test%'' ')
SET pgvalue = tabref.myvalue
FROM tabref
WHERE tabref.mykey =  pgkey