Date: Thu, 12 Jan 2023 19:09:27 +0100 (CET)
From: JP Pozzi <jpp@jppozzi.dyndns.org>
To: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org
Message-ID: <382249592.381.1673546967170.JavaMail.zimbra@jppozzi.dyndns.org>
In-Reply-To: <bb68633a-28d7-2a46-f815-c64dbb2660d2@jakobs.com>
References: 
 <CANhObs2JcdUmxm_gVEpqbCe76_v18R5JAEtTrU5B5wPCx4DNOg@mail.gmail.com>
 <CAAo1mbmfyHdfkNDsEs=SDjSdU4kssWi9xJvLsdL9cCgNtd5FKw@mail.gmail.com>
 <CANhObs1R4LFy0DHZCf-FjOch+yUpDyP-59Zd+hDRkp=ZGfkuzg@mail.gmail.com>
 <CAKPjcsGseO8qA7fi=HN=xWeqoRbAWhXixdv26HVch_xp8R42hQ@mail.gmail.com>
 <bb68633a-28d7-2a46-f815-c64dbb2660d2@jakobs.com>
Subject: Re: MSSQL to PostgreSQL Migration
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: MSSQL to PostgreSQL Migration
Thread-Index: JUshv2diEqLeYpnl7DO7mDB+XiCI5g==
Archived-At: 
 <https://www.postgresql.org/message-id/382249592.381.1673546967170.JavaMail.zimbra%40jppozzi.dyndns.org>
Precedence: bulk

Hello,

You can also look at Postgresql Foreign Data Wrapper (FDW) which can give a=
ccess to
"foreign" tables even for MSSQL.
The transfer will be immediate as :
insert into postgres_table select * from mssql_table;

Regards

JP P

----- Mail original -----
De: "Holger Jakobs" <holger@jakobs.com>
=C3=80: pgsql-admin@lists.postgresql.org
Envoy=C3=A9: Jeudi 12 Janvier 2023 18:37:08
Objet: Re: MSSQL to PostgreSQL Migration

Am 12.01.23 um 04:21 schrieb Gurudutt Dhareshwar:
> For data you can move it using the SQL tool itself=C2=A0or take a BCP Out=
=20
> and then insert using the psql -d option .


Actually, bcp.exe does not do a very good job. It cannot make a=20
difference between an empty string and a NULL value, for instance.

If you try to export in tab-separated format=20
(https://www.iana.org/assignments/media-types/text/tab-separated-values),=
=20
carriage returns, newlines, tab character will all mess up the output.=20
And contained backslashes are not doubled as necessary for PG's copy=20
statement.

In case your tables aren't too large, you can export via PowerShell=20
keeping the differences between NULL and empty string. The way via JSON=20
may be a bit slow and heavy on memory, though.


### Code for Powershell

install-module sqlserver

$SqlParams =3D @{
 =C2=A0 ServerInstance =3D 'server_name'
 =C2=A0 UserName =3D 'user_name'
 =C2=A0 Password =3D 'very_secret'
 =C2=A0 Database =3D 'db_name'
}

$tableName =3D 'whatever_tablename'
(invoke-sqlcmd @SqlParams -query "Select * from $tableName" |=20
select-object * -excludeproperty=20
itemarray,table,rowerror,rowstate,haserrors |=20
convertto-json).replace('null', '"=C2=A7n=C2=A7"') | convertfrom-json | exp=
ort-csv=20
-path "$($tableName).csv" -usequotes asneeded


### import using psql
\copy whatever_tablename from whatever_tablename.csv (format csv, header=20
on);

Kind Regards,

Holger


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012