I have a requirement of transferring huge amount of data (nearly 10 million records) from one database to another database using Java Program. I have done the same by creating connections with the two DB and queried the data from the source db and then inserted the data into destination db by iterating the result set. But It's taking huge time to transfer the data. Is there any way to do the transfer process quickly?
So I know that you can do an INSERT/SELECT in a single statement. So something like:
However, I'm not sure how that would work using JDBC except for the fact that with MYSQL if the databases are on the same server and share the same credentials, it would work. Not sure about other databases though.
Aside from that, you'll probably just have to do your select and then batch the results. And it isn't going to be fast.
I have used Gregg's solution and it worked well in MySQL, not sure how it would work in other databases.
Another option may be to use the incomplete-but-free DbTamer (in my signature) as this is one of the problems it is designed to manage.
Try Gregg's first as something that runs completely within the DB will be orders of magnitude faster.
When data size is huge I would prefer vendor specific solutions in first place if it is possible. Export/import, Database links, ODI etc.
But eventually your requirements will setup the solution base! What are the source and target database vendors,versions? Does the process need to be applied against changing databases, or in other words will it be generic?
Joined: Jan 28, 2009
Thanks for your reply,
I have to transfer data from SAS Dataset into Oracle tables. Due to some security restrictions we are not allowed to connect to oracle database from SAS Server to upload data. Hence we decided to use Java program to transfer the data, which leads to performance issue.
Antany Vasanth wrote: we decided to use Java program to transfer the data, which leads to performance issue.
The performance issue is likely the network/data transfer rather than the Java program itself. Is there anything you can do to get one of the network hops out of the picture or make the data more compressed before transferring it to your Java program?