hi all,
right now i'm trying to migrate from db2 running under linux to mysql v5.1.
i manage to export out the db2 structure & data into a del (ascii) file.
but when i try to load the data from the del file to mysql table, it generate an error.
below is the load data infile syntax i use =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
and below is the sample layout of del file (in the above case is TABLE01.del) =
1,0,"2007-07-31 00:25:12"
2,0,"2007-07-31 14:09:00"
3,0,"2007-07-31 00:00:00"
4,0,"2007-07-31 00:00:00"
5,0,"2007-07-31 00:00:00"
and below is the TABLE01 structure =
FieldName, Type, Null, Primary
------------------------------------------------------
MY_TABLEID, int(11), NO, PRIMARY KEY
CNTS, int(11), NO, NOT PRIMARY KEY
INSERT_DATE, datetime, NO, PRIMARY KEY
the error i encounter is =
ERROR 1292 (22007): Incorrect datetime value: '"2007-07-31 00:25:12".....
i do some trick by trying to remove the qoute sign from "2007-07-31 00:25:12" so the del file become =
1,0,2007-07-31 00:25:12
2,0,2007-07-31 14:09:00
3,0,2007-07-31 00:00:00
4,0,2007-07-31 00:00:00
5,0,2007-07-31 00:00:00
And i do load data infile again with same command =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
and it was successful.
to me it is very strange, since i can do a sql insert using "2007-07-31 00:25:12" for example: insert into TABLE01 values (11, 22, "2007-07-31 00:25:12") successfully without any error!!!
in fact, when i use a MySQL Administrator tools, and i export out the data from TABLE01 into CSV, then i found that the structure is exactly the same with del file generated by db2.
do i make any mistake??
or do i need to initialize something before i call
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????
Any help is appreciated.
Thank you.
Regards,
Lanes