DB間csvインポート

• MySQL

(8.0.29)
show variables like 'secure_file_priv';

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

insert into tab1 values(1.01, 'あ', '1234567890', now(3) );
insert into tab1 values(1.02, ',', '', now(3) );
insert into tab1 values(1.03, 'X', NULL, now(3) );

select * from tab1;

-- MySQL,Oracle,PostgreSQL用csvファイル作成

select * into outfile '/tmp/a.csv'
fields terminated by ',' optionally enclosed by '"'
from tab1;

-- SQL Server用csvファイル作成
select * into outfile '/tmp/b.csv'
fields terminated by '|'
from tab1;

yum -y install nkf
nkf -s -Lw b.csv > c.csv

-- ①MySQL -> MySQL

show variables like 'secure_file_priv';

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

load data infile '/tmp/a.csv' into table tab1
fields terminated by ',' optionally enclosed by '"'
;

select * from tab1;

-- ②MySQL -> Oracle

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

vim a.ctl

load data
infile '/tmp/a.csv'  "str '\n'"
into table tab1
insert
fields terminated by ','
optionally enclosed by '"'
NULLIF = "\\N"
trailing nullcols
( col1
 ,col2 char(3)
 ,col3 char(10)
 ,col4 timestamp "YYYY/MM/DD HH24:MI:SS.FF3"
)

sqlldr test/test@pdb1 control=a.ctl log=a.log

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';
select * from tab1;

-- ③MySQL -> PostgreSQL

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

\copy tab1 from '/tmp/a.csv' with (format csv, null '\N');

\pset null '<<null>>'

select * from tab1;

-- ④MySQL -> SQL Server

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

bcp test.dbo.tab1 in "C:\c.csv"  -T -c -t"|"

select * from tab1;

-- 空文字とヌルの修正
update tab1 set col3 = '' where col3 is null;
update tab1 set col3 = null where col3 = '\N';

• Oracle

(19c)

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';

insert into tab1 values(1.01, 'あ', '1234567890', systimestamp);
insert into tab1 values(1.02, ',', '', systimestamp);
insert into tab1 values(1.03, 'X', NULL, systimestamp);

commit;
select * from tab1;

-- MySQL,Oracle,PostgreSQL用csvファイル作成

sqlplus -S test/test@pdb1 <<EOF
alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';
set head off
set markup csv on
set trimspool off
set echo off
spool a.csv
select * from tab1;
spool off
EOF

cat a.csv
sed -i '/^$/d' a.csv
cat a.csv

-- SQL Server用csvファイル作成

sqlplus -S test/test@pdb1 <<EOF
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';
set head off
set markup csv on delimiter | quote off
set trimspool off
set echo off
spool b.csv
select * from tab1;
spool off
EOF

cat b.csv
sed -i '/^$/d' b.csv
cat b.csv

sudo yum install -y nkf

nkf -s -Lw b.csv > c.csv

-- ⑤Oracle -> MySQL

show variables like 'secure_file_priv';

vim /etc/my.cnf
secure_file_priv = ''

systemctl restart mysqld

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

load data infile '/tmp/a.csv' into table tab1
fields terminated by ',' optionally enclosed by '"'
;

select * from tab1;

ヌルは空文字に移行される

-- ⑥Oracle -> Oracle

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

vim a.ctl

load data
infile 'a.csv'  "str '\n'"
into table tab1
insert
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( col1 
 ,col2 char(3)
 ,col3 char(10)
 ,col4 timestamp "YYYY/MM/DD HH24:MI:SS.FF3"
)

sqlldr test/test@pdb1 control=a.ctl log=a.log

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';
select * from tab1;

-- ⑦Oracle -> PostgreSQL

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

\copy tab1 from 'a.csv' with (format csv, null '');

\pset null '<<null>>'

select * from tab1;

ヌルはヌルに移行される

-- ⑧Oracle -> SQL Server

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

bcp test.dbo.tab1 in "C:\c.csv"  -T -c -t"|"

select * from tab1;

ヌルはヌルに移行される

• PostgreSQL

(14)

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

insert into tab1 values(1.01, 'あ', '1234567890', clock_timestamp() );
insert into tab1 values(1.02, ',', '', clock_timestamp() );
insert into tab1 values(1.03, 'X', NULL, clock_timestamp() );

\pset null '<<null>>'

select * from tab1;

-- Oracle,PostgreSQL用csvファイル作成

\copy tab1 to '/tmp/a.csv' with ( format csv,  null '', quote '"' );

-- MySQL用csvファイル作成

\copy tab1 to '/tmp/b.csv' with ( format csv,  null "\N", quote '"' );

-- SQL Server用csvファイル作成

\copy tab1 to '/tmp/c.csv' with ( format csv,  null '', delimiter "|" );

cat c.csv

sudo dnf -y --enablerepo=powertools install nkf

nkf -s -Lw c.csv > d.csv

-- ⑨PostgreSQL -> MySQL

show variables like 'secure_file_priv';

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

load data infile '/tmp/b.csv' into table tab1
fields terminated by ',' optionally enclosed by '"'
;

select * from tab1;

-- ⑩PostgreSQL -> Oracle

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

vim a.ctl

load data
infile '/tmp/a.csv'  "str '\n'"
into table tab1
insert
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( col1 
 ,col2 char(3)
 ,col3 char(10)
 ,col4 timestamp "YYYY-MM-DD HH24:MI:SS.FF3"
)

sqlldr test/test@pdb1 control=a.ctl log=a.log

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';
select * from tab1;

-- ⑪PostgreSQL -> PostgreSQL

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

\copy tab1 from '/tmp/a.csv' with ( format csv,  null '', quote '"' );

\pset null '<<null>>'

select * from tab1;

-- ⑫PostgreSQL -> SQL Server

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

bcp test.dbo.tab1 in "C:\d.csv"  -T -c -t"|"

select * from tab1;

-- 空文字の修正
update tab1 set col3 = '' where col3 = '""';

• SQL Server

(2019)

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

insert into tab1 values(1.01, 'あ', '1234567890', getdate() );
insert into tab1 values(1.02, ',', '', getdate() );
insert into tab1 values(1.03, 'X', NULL, getdate() );

select * from tab1;

bcp test.dbo.tab1 out "C:\a.csv"  -T -c -t"|"

-- ⑬SQL Server -> MySQL

nkf -w -Lu a.csv > b.csv

SQL Serverのbcpで出力した空文字には\0がセットされている

od -c b.csv
grep -Pa "\x00" b.csv

cat b.csv | sed 's/^|/\\N|/g' | sed 's/||/|\\N|/g'  | sed 's/|$/|\\N/g'  > c.csv
sed -i 's/\x00//g' c.csv

show variables like 'secure_file_priv';

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 datetime(3) );

load data infile '/tmp/c.csv' into table tab1
fields terminated by '|' optionally enclosed by '"'
;

select * from tab1;

-- ⑭SQL Server -> Oracle

nkf -w -Lu a.csv > b.csv

SQL Serverのbcpで出力した空文字には\0がセットされている

od -c b.csv
grep -Pa "\x00" b.csv

sed -i 's/\x00/""/g' b.csv

drop table tab1 purge;
create table tab1(col1 number(10,2), col2 varchar2(3), col3 varchar2(10), col4 timestamp(3) );

vim a.ctl

load data
infile '/tmp/b.csv'  "str '\n'"
into table tab1
insert
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
( col1 
 ,col2 char(3)
 ,col3 char(10)
 ,col4 timestamp "YYYY-MM-DD HH24:MI:SS.FF3"
)

sqlldr test/test@pdb1 control=a.ctl log=a.log

alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF3';
select * from tab1;

-- ⑮SQL Server -> PostgreSQL

nkf -w -Lu a.csv > b.csv

SQL Serverのbcpで出力した空文字には\0がセットされている

od -c b.csv
grep -Pa "\x00" b.csv

sed -i 's/\x00/""/g' b.csv

drop table tab1;
create table tab1(col1 numeric(10,2), col2 varchar(1), col3 varchar(10), col4 timestamp(3) );

\copy tab1 from '/tmp/b.csv' with ( format csv,  null '', quote '"' , delimiter '|');

\pset null '<<null>>'

select * from tab1;

-- ⑯SQL Server -> SQL Server

drop table tab1;
create table tab1(col1 numeric(10,2) , col2 nvarchar(1), col3 varchar(10), col4 datetime2(3) );

bcp test.dbo.tab1 in "C:\a.csv"  -T -c -t"|"

select * from tab1;