Re: MSSQL to PostgreSQL Migration

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

You can also look at Postgresql Foreign Data Wrapper (FDW) which can give access 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@xxxxxxxxxx>
À: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Envoyé: 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 or take a BCP Out 
> and then insert using the psql -d option .


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

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

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


### Code for Powershell

install-module sqlserver

$SqlParams = @{
   ServerInstance = 'server_name'
   UserName = 'user_name'
   Password = 'very_secret'
   Database = 'db_name'
}

$tableName = 'whatever_tablename'
(invoke-sqlcmd @SqlParams -query "Select * from $tableName" | 
select-object * -excludeproperty 
itemarray,table,rowerror,rowstate,haserrors | 
convertto-json).replace('null', '"§n§"') | convertfrom-json | export-csv 
-path "$($tableName).csv" -usequotes asneeded


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

Kind Regards,

Holger


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






[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux