2
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 =
Expand|Select|Wrap|Line Numbers
- 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) =
Expand|Select|Wrap|Line Numbers
- 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 =
Expand|Select|Wrap|Line Numbers
- 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 =
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
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
Expand|Select|Wrap|Line Numbers
- LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????
Thank you.
Regards,
Lanes
