改行を含むデータの移送

• MySQL

(8.0.29)

tab1 -> 移行元(改行置換前)
tab2 -> 移行元(改行置換後)
tab3 -> 移行先(改行置換後)
tab4 -> 移行先(改行置換前)

drop table tab1 ;
create table tab1(col1  int  primary key
                 ,col2  datetime(0)
                 ,col3  datetime(6)
                 ,col4  varchar(100)
                 );

drop table tab2 ;
create table tab2(col1  int  primary key
                 ,col2  datetime(0)
                 ,col3  datetime(6)
                 ,col4  varchar(100)
                 );

drop table tab3 ;
create table tab3(col1  int  primary key
                 ,col2  datetime(0)
                 ,col3  datetime(6)
                 ,col4  varchar(100)
                 );

drop table tab4 ;
create table tab4(col1  int  primary key
                 ,col2  datetime(0)
                 ,col3  datetime(6)
                 ,col4  varchar(100)
                 );

drop procedure proc1;

delimiter //
create procedure proc1()
begin
  declare i bigint default 1;
  start transaction;
  while i <= 100000 do
    insert into tab1(col1,col2,col3,col4) values(i ,now(0),now(6),concat('A',CHAR(10),'B') );
    set i = i+1;
  end while;
  commit;
end
//
delimiter ;

call proc1();

select count(*) from tab1;
select * from tab1 where col1 = 1;

insert into tab2
select col1,col2,col3,replace(col4,CHAR(10),'@') col4
from tab1;

select count(*) from tab2;
select * from tab2 where col1 = 1;

show variables like 'secure_file_priv';

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

wc -l tab2.csv

head tab2.csv

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

select count(*) from tab3;
select * from tab3 where col1 = 1;

insert into tab4 select col1,col2,col3,replace(col4,'@',CHAR(10) )  col4 from tab3;

select count(*) from tab4;
select * from tab4 where col1 = 1;

select * from tab1 t1 left join tab4 t4
on t1.col1 = t4.col1
and t1.col2 = t4.col2
and t1.col3 = t4.col3
and t1.col4 = t4.col4
where t4.col1 is null;

select * from tab4 t4 left join tab1 t1
on t4.col1 = t1.col1
and t4.col2 = t1.col2
and t4.col3 = t1.col3
and t4.col4 = t1.col4
where t1.col1 is null;

• Oracle

(19c)

tab1 -> 移行元(改行置換前)
tab2 -> 移行元(改行置換後)
tab3 -> 移行先(改行置換後)
tab4 -> 移行先(改行置換前)

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

drop table tab1 purge;
create table tab1(col1  int  primary key
                 ,col2  date
                 ,col3  timestamp(6)
                 ,col4  varchar2(100)
                 );

drop table tab2 purge;
create table tab2(col1  int  primary key
                 ,col2  date
                 ,col3  timestamp(6)
                 ,col4  varchar2(100)
                 );

drop table tab3 purge;
create table tab3(col1  int  primary key
                 ,col2  date
                 ,col3  timestamp(6)
                 ,col4  varchar2(100)
                 );

drop table tab4 purge;
create table tab4(col1  int  primary key
                 ,col2  date
                 ,col3  timestamp(6)
                 ,col4  varchar2(100)
                 );

declare
begin
for i in 1..100000 loop
  insert into tab1(col1,col2,col3,col4) values(i ,sysdate,systimestamp,'A'||CHR(10)||'B');
end loop;
end;
/
commit;

select count(*) from tab1;
select * from tab1 where col1 = 1;

insert into tab2
select col1,col2,col3,replace(col4,CHR(10),'@') col4
from tab1;

commit;

select count(*) from tab2;
select * from tab2 where col1 = 1;

sqlplus -S test/test@pdb1 <<EOF > /dev/null
alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF9';
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
set head off
set trimspool on
set echo off
set feed off
set long 2000000000
set longchunksize  2000000000
set linesize 32767
set pagesize 0
set markup csv on
spool tab2.csv

select * from tab2;

spool off
EOF

wc -l tab2.csv

head tab2.csv

vim tab3.ctl

LOAD DATA 
INFILE '/home/oracle/tab2.csv' "str '\n'"
insert
INTO TABLE "TEST"."TAB3"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
"COL1" ,
"COL2" DATE "YYYY/MM/DD HH24:MI:SS" ,
"COL3" TIMESTAMP "YYYY/MM/DD HH24:MI:SS.FF9" ,
"COL4" CHAR (100) )

sqlldr test/test@pdb1 control=tab3.ctl

select count(*) from tab3;
select * from tab3 where col1 = 1;

insert into tab4 select col1,col2,col3,replace(col4,'@',CHR(10) )  col4 from tab3;
commit;

select count(*) from tab4;
select * from tab4 where col1 = 1;

select * from tab1
minus
select * from tab4;

select * from tab4
minus
select * from tab1;

• PostgreSQL

(14)

tab1 -> 移行元(改行置換前)
tab2 -> 移行元(改行置換後)
tab3 -> 移行先(改行置換後)
tab4 -> 移行先(改行置換前)

drop table tab1 ;
create table tab1(col1  int  primary key
                 ,col2  timestamp(0)
                 ,col3  timestamp(6)
                 ,col4  varchar(100)
                 );

drop table tab2 ;
create table tab2(col1  int  primary key
                 ,col2  timestamp(0)
                 ,col3  timestamp(6)
                 ,col4  varchar(100)
                 );

drop table tab3 ;
create table tab3(col1  int  primary key
                 ,col2  timestamp(0)
                 ,col3  timestamp(6)
                 ,col4  varchar(100)
                 );

drop table tab4 ;
create table tab4(col1  int  primary key
                 ,col2  timestamp(0)
                 ,col3  timestamp(6)
                 ,col4  varchar(100)
                 );

insert into tab1 select g,current_timestamp(0), current_timestamp(6),'A'||chr(10)||'B' from generate_series(1,100000) g;

select count(*) from tab1;
select * from tab1 where col1 = 1;

insert into tab2
select col1,col2,col3,regexp_replace(col4,'\n','@') col4
from tab1;

select count(*) from tab2;
select * from tab2 where col1 = 1;

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

wc -l tab2.csv

head tab2.csv

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

select count(*) from tab3;
select * from tab3 where col1 = 1;

insert into tab4 select col1,col2,col3,regexp_replace(col4,'@',CHR(10) ) col4 from tab3;

select count(*) from tab4;
select * from tab4 where col1 = 1;

select * from tab1
except
select * from tab4;

select * from tab4
except
select * from tab1;

• SQL Server

(2019)

tab1 -> 移行元(改行置換前)
tab2 -> 移行元(改行置換後)
tab3 -> 移行先(改行置換後)
tab4 -> 移行先(改行置換前)

drop table tab1 ;
create table tab1(col1  int not null primary key
                 ,col2  datetime2(0)
                 ,col3  datetime2(6)
                 ,col4  varchar(100)
                 );

drop table tab2 ;
create table tab2(col1  int not null primary key
                 ,col2  datetime2(0)
                 ,col3  datetime2(6)
                 ,col4  varchar(100)
                 );

drop table tab3 ;
create table tab3(col1  int not null primary key
                 ,col2  datetime2(0)
                 ,col3  datetime2(6)
                 ,col4  varchar(100)
                 );

drop table tab4 ;
create table tab4(col1  int not null primary key
                 ,col2  datetime2(0)
                 ,col3  datetime2(6)
                 ,col4  varchar(100)
                 );

insert into tab1 select 1,getdate(), getdate(),'A'+char(10)+'B';

set nocount on
declare @i bigint = 1;
begin transaction;
while @i <= 100000
begin
  insert into tab1 select @i,getdate(), getdate(),'A'+char(10)+'B';
  set @i = @i + 1;
end
commit;

select count(*) from tab1;
select * from tab1 where col1 = 1;

insert into tab2
select col1,col2,col3,replace(col4,char(10),'@') col4
from tab1;

select count(*) from tab2;
select * from tab2 where col1 = 1;

bcp test.dbo.tab2 out "C:\tab2.csv"  -T -c -t","

bcp test.dbo.tab3 in "C:\tab2.csv"  -T -c -t","

select count(*) from tab3;
select * from tab3 where col1 = 1;

insert into tab4 select col1,col2,col3,replace(col4,'@',char(10) ) col4 from tab3;

select count(*) from tab4;
select * from tab4 where col1 = 1;

select * from tab1
except
select * from tab4;

select * from tab4
except
select * from tab1;