Forgive me if this question is a bit too generic, if it is, feel free to
just not respond.

I have a database which has been running in PostgreSQL for a number of
years at this stage which, for reasons beyond my control, I need to port
into MS SQL server.

It seems that the SQL that Postgre outputs when I do a backup is not
syntactically correct within MS-SQL server. (and vice versa - kudos to
microsoft for not sticking to standards)

My question is, does anyone have any documentation on how to convert a
database from the Postgre platform to SQL server? I can see tools on the
postgre website to convert the other way, but nothing going backwards again.

Failing this, does anyone have any suggestions on where I might start -
I did attempt to go through the SQL code and modify it to suit SQL
server, but it's about 3,500 lines of code excluding the insert
statements (which themselves are also wrong) and almost every line needs
something changed when comparing SQL syntax from Postgre to MSSQL server

Thanks in advance for any comments/suggestions.

Engada.

--
Posted via a free Usenet account from http://www.teranews.com

Engada <plengada (AT) optusnet (DOT) com.au> wrote:
My answer is probably very generic too, so apologies if it does not
help you a lot.

PostgreSQL also is not standard compliant, although it is always a goal.
Yet in a backup, you will certainly have to include things that are
beyond the standard (e.g. commands to set the database encoding to whatever
the backup is done in).

This is hardly surprising, as the PostgreSQL people are not very
interested in this kind of migration.

Maybe there are newsgroups or similar for Microsoft SQL Server - you
might find more helpful information there (Microsoft should be interested
in this kind of conversion).

First, try to concentrate on the DDL-commands (essentially the non-INSERT
commands). There should not be too many of them. Try to change those to
the 'other' SQL syntax.

The INSERT commands shouldn't be a great problem, but they are too many
to change by hand. Since the syntax of INSERT is comparatively simple,
I would be interested to know where the problem is.

I can for example imagine that the string representation for a date or
timestamp makes Microsoft SQL server choke. Mind you, this is only an
example, as I know nothing about Microsoft's SQL syntax.

Since all the INSERT lines will look similar, you can probably fix them
all at once with a single command in vi (or similar editors).

That's the way I do things like that, maybe it helps you.

Yours,
Laurenz Albe