Linked server as we discussed in our prior article. OPENQUERY - needs a reference to an already estabilished Linked server.SQL Server provides two functions for doing cross server calls or pulling in files. Anyrate with that said lets move on with the show.Īlthough this example is focused on using PostgreSQL with Microsoft SQL Server, the same technique applies whenĬopying retrieving updating data from other databases such as MySQL or Oracle or DB II. The OPENROWSET and OPENQUERY logic that SQL Server provides is just simply better and easier to use than the dblink provided for PostgreSQL. Its just a little easier from Microsoft SQL Server. The other question of why triggering from SQL Server instead of PostgreSQL is because 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),Īnd there are numerous other reasons that are too hard to itemize. 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 There are many reasons for having multiple DBMS providers inĪn organization. We all would like to think we are an island and live in a world with one DBMS system, but we don't. Why on earth would you want to copy data back and forth between 2 servers and 2 disparate DBMS systems for that matter? 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. While OPENROWSET is not necessarily the fasted, In this article we shall demonstrate using Microsoft SQL Server 2005/2008 OPENQUERY AND OPENROWSET to add, delete and update data in PostgreSQL.įirst we must start by saying there are a number of ways of copying data between databases. 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
0 Comments
Leave a Reply. |