File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC for loading data into  SQL Server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC for loading data into  SQL Server" Watch "JDBC for loading data into  SQL Server" New topic
Author

JDBC for loading data into SQL Server

Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Hello!
I'm loadinf data from Oracle9i dab into Microsoft SQL Server 2000 using JDBC.
At the end of he INSERT I disaply a JOptionPane..... saying that
INSERT was successfull.

Everything is fine when selecting and inserting about 150-200 records
and JOptionPane... is displayed
Truly didnt try 1000-2000 records yet, but when doing the same and
setting the number of records to approx 40.000 it doesnt want to.
Nither JOptionPane....is displayed nor exception is thrown.
Cannot really find out the reason.
Could you please say what could be the possible reasons?
Thank you very much.
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Forgot to say that using addBatch and execute Batch when inserting
Thank you.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14150
    
  18

Remedy Remedy, please check your private messages for an important administrative matter. You can see them by clicking My Private Messages.


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 7 API documentation
Scala Notes - My blog about Scala
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
I apologize for being not careful.
I changed it.
Thank you for warning.
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Somebody please suggest something.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Mostly likely, in my opinion, is that you are catching an exception and not reporting it.

Can you please re-read Jesper's PM request? Your display name should have a space, not an underscore in it.

thanks,
Dave
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Thanks, updated profile and thanks for the answer.

What I'm doing is inserting at the same time when selecting.
Just tried on 1000 rows, wokrs about 15 minutes but fine and rows inserted, increasing the number of rows to 40.000 doenst react to anything.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

You said 1000 takes 15 minutes, so you would expect 40x1000 to take in the order of 40x15 minutes (a long time)

It is possible that he amount of data is taking time to transfer between machines (I am assuming a remote DB) and it is also possible that the database is not optimised for inserts (eg lots and lots of indexes).

You may want to run it for an hour or two, with batches of size 1000 or less, and monitor the db to make sure data keeps getting inserted. We once had a DB load operation that took over 12 hours.

Maybe there is no error as there is nothing wrong?
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Thank you very much, I solved it.
Instead of reading the selected data into an array I
write it to a text file then insert data from it into SQL Server
using BULK INSERT.

Thanks.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Congrats, thanks for coming back to us to share the solution
Fadai Ganjaliyev
Greenhorn

Joined: May 19, 2008
Posts: 22
Hi, again.
How to close a topic?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

We don't we just leave it here
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC for loading data into SQL Server