This
example shows how to copy a table from Oracle to MySQL. First create a
table in Oracle and add some data, including some null values and a date
column:
SQL> create table andrews_table
2 (first_name varchar2(10),
3 dob date,
4 salary number)
5 /
Table created.
SQL> insert into andrews_table values
2 ('Fred', '25-DEC-1980', null)
3 /
1 row created.
SQL> insert into andrews_table values
2 ('Jim', null, 10000)
3 /
1 row created.
SQL> insert into andrews_table values
2 (null, '01-SEP-1983', 20000)
3 /
1 row created.
SQL> select * from andrews_table
2 /
FIRST_NAME DOB SALARY
---------- --------- ----------
Fred 25-DEC-80
Jim 10000
01-SEP-83 20000
SQL>
Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:
UNIX > cat extract.sql
set feedback off
set lines 100
set pages 0
set termout off
set trimspool on
spool extract
select nvl(first_name,'\N')||'|'||
nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||
nvl(to_char(salary),'\N')
from andrews_table
/
spool off
UNIX >
... and run the SQL:
SQL> @extract
SQL>
The output file will look like this:
UNIX > cat extract.lst
Fred|1980-12-25|\N
Jim|\N|10000
\N|1983-09-01|20000
UNIX >
Copy it to a machine running MySQL then you can load it into a table as follows:
--------------
create table andrews_table
(first_name varchar(10),
dob date,
salary int)
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
load data local infile 'extract.lst'
into table andrews_table
fields terminated by '|'
lines terminated by '\n'
(first_name, dob, salary)
--------------
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
--------------
select * from andrews_table
--------------
+------------+------------+--------+
| first_name | dob | salary |
+------------+------------+--------+
| Fred | 1980-12-25 | NULL |
| Jim | NULL | 10000 |
| NULL | 1983-09-01 | 20000 |
+------------+------------+--------+
3 rows in set (0.00 sec)
mysql>
