Re: MSSQL to PgSQL
Re: MSSQL to PgSQL
От:
"Anthony E . Greene" <agreene@pobox.com>
Дата:
On Wed, 03 Jan 2001 18:40:23 Mike Hammonds wrote: >Can PgSQL use SQL scripts generated from MSSQL? see the attached script I've used basic SQL queries interchangeably in psql and iSQL. They do need to be plain text files though: file energy2.sql energy2.sql: MP3, 80 kBits2, 96 kBits, 32 kHz, Stereo -- Anthony E. Greene PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D Chat: AOL/Yahoo: TonyG05 ICQ: 91183266 Linux. The choice of a GNU Generation.
Re: MSSQL to PgSQL
От:
hubert depesz lubaczewski <depesz@depesz.pl>
Дата:
On Wed, Jan 03, 2001 at 11:45:22PM -0500, Anthony E . Greene wrote:
> I've used basic SQL queries interchangeably in psql and iSQL. They do need
> to be plain text files though:
> file energy2.sql
> energy2.sql: MP3, 80 kBits2, 96 kBits, 32 kHz, Stereo
just open it in vim and
:%s/^V^J//g
(this command will render as:)
:%s/^@//g
and now the code is quite readable.
but i thinks that sending that big code to mailing list is somehow sick.
depesz
--
hubert depesz lubaczewski
------------------------------------------------------------------------
najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: MSSQL to PgSQL
От:
Andrew McMillan <Andrew@catalyst.net.nz>
Дата:
Mike Hammonds wrote:
>
> ** Reply Requested When Convenient **
>
> Can PgSQL use SQL scripts generated from MSSQL? see the attached script
>
> Mike Hammonds, CNE5, MCP-NT
> Fellon-McCord & Associates, Inc.
> Office: 502.214-6324
> Fax: 502.426.8800
> mhammonds@knowledgeinenergy.com
> Show Me the Code!!
>
> ------------------------------------------------------------------------
> Name: energy2.sql
> energy2.sql Type: unspecified type (application/octet-stream)
> Encoding: base64
Well, no matter what 'file' says that ain't an MP3 :-)
After I used 'tr' to get the nulls out I can see that it won't work in
PostgreSQL because:
IF (SELECT COUNT(*) FROM deleted, tblDealLog
WHERE (deleted.ContactNo = tblDealLog.ContactNo)) > 0
BEGIN
RAISERROR(778584, 16, 1)
ROLLBACK TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
1) PostgreSQL doesn't use 'GO' as an end of command indicator, it uses a
semi-colon - that should be a fairly straightforward replacement.
2) PostgreSQL doesn't have a syntax like that for any RAISERROR
function. But you could implement a function called RAISERROR which
took three parameters and did something with them.
3) PostgreSQL will not allow multiple SET ... commands without normal
statement separators between them. I don't think that those ones would
be valid in PostgreSQL anyway.
CREATE TRIGGER tblVendorContacts_UTrig ON tblVendorContacts FOR UPDATE
AS
/*
* PREVENT UPDATES IF NO MATCHING KEY IN 'tblVendorAddress'
*/
IF UPDATE(VendorOwnerNo)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblVendorAddress, inserted WHERE
(tblVendorAddress.VendorOwnerNo = inserted.VendorOwnerNo))
BEGIN
RAISERROR(778573, 16, 1)
ROLLBACK TRANSACTION
END
END
4) PostgreSQL syntax for triggers is substantially different to this,
viz:
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
which is not too surprising as there is no CREATE TRIGGER in the SQL-92
standard (according to PostgreSQL docs.
5) PostgreSQL does not have "BEGIN" or "IF" constructs in SQL (well,
there is "CASE") but that's OK since they are in PL/PGSQL, which is what
most medium complexity trigger procedures are going to end up written in
anyway.
Everything else in the file seems to be pretty plain SQL, which should
pretty much work, except that making these conversions might not be
sensible. The SQL in that file appears to implement things like
referential integrity constraints, and in PostgreSQL may well be better
implemented as such constraints, specified within the CREATE TABLE
statement.
There are some tools for converting databases from MS SQL to PostgreSQL
as well. I haven't used them, I'm afraid, but I did look into it and
some of them seem particularly good.
I recently had to convert an MS SQL database to PostgreSQL and I chose
to do it by dumping the SQL to build the database and then hand-editing
it into a PostgreSQL database, editing out some existing data design
flaws in the process. Once I had the data structures set up to my
liking I actually wrote a conversion process that exported the MS SQL
data into CSV and then used Perl and SQL to load it and massage it into
the PostgreSQL database.
While this may all sound pretty laborious, I was also converting the
application from ASP to PHP at the same time, so there was definitely
value in fixing design flaws at the same time. If you are not dependant
on having exactly the same set of tables and fields I would recommend
the approach.
Regards,
Andrew McMillan
PS. Good luck!
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
MSSQL to PgSQL
От:
"Mike Hammonds" <mhammonds@knowledgeinenergy.com>
Дата:
** Reply Requested When Convenient ** Can PgSQL use SQL scripts generated from MSSQL? see the attached script Mike Hammonds, CNE5, MCP-NT Fellon-McCord & Associates, Inc. Office: 502.214-6324 Fax: 502.426.8800 mhammonds@knowledgeinenergy.com Show Me the Code!!
