I have a table with 5,000,000 records and I want select several fields out and insert to a MYSQL database table . So I concat a SQL to insert . But there are too many records MYSQL can't import one time (The file is more than 700M). So I should commit every N rows . I want to find a good method to solve this problem .
I use PL/SQL to execute my SQL statement . I also find when I export a table using PL/SQL I can select commit every N rows . I think if I know how PL/SQL implement it I will know how to do .
Any answer is appreciative .
What are you using for the connection between Oracle and MySQL ? Is the Database Gateway for ODBC (DG4ODBC) ? If not, then what are you using ?
If you are using PL/SQL for the insert statements then you could add a counter for every insert statement then issue a commit when the counter reaches a certain value.
If this doesn't make sense then post the PL/SQL you are using and we maybe able to help further.
There is no connection between Oracle and MySQL . I want to use PL/SQL to connect to Oracle and select & concat out lots of insert statements to a SQL file and then import the SQL file to MySQL by mysql command .
Yes , I am using PL/SQL and it has a Export Tables tool . But I find it can only export all the table fields and the insert SQL can not be defined .
Of cause , I can modify the SQL file by Unix command , but I wonder if I can get a better method .
Thanks for the clarification.
As this is a problem using PL/SQL itself it would be better to ask the question in the PL/SQL forum here - SQL and PL/SQL
They will have more experience of using PL/SQL itself to do what you want.
If I understand correctly this is what you want to do -
- create insert statements from data in Oracle tables
- run these insert statements directly in MySQL using the MySQL equivalent of SQLPLUS
- you want the insert statements to have a commit after 'x' number of rows
If this is the case I still think the best option is to raise it in the SQL-PL/SQL forum.
The issue is creating the insert statements and adding an 'commit' statement after a number of rows.
I would have thought depending how you write the insert statements it should be possible to have some of counter and add a 'commit' statement after a number of rows.
Try the SQL-PL/SQL forum for further advice on the best way to do this.
Thanks for your generous answers . Maybe Oracle or SQL is not good at this work .
I think it's also a good method to finish the work using Unix command .
I will also try to find out how the PL/SQL implements it .