aspose file tools*
The moose likes JDBC and the fly likes transfer data from one DB to another Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "transfer data from one DB to another" Watch "transfer data from one DB to another" New topic
Author

transfer data from one DB to another

Des Barron
Greenhorn

Joined: May 02, 2005
Posts: 3
Hi,

I need to select data from one database (AS400 or SQL) and insert into another database (Access) I have done this in VB but getting it to work with JDBC is more difficult. I think I need to load two drivers? I do not want to select the records I want and then scroll through each result and run an insert statement. I want it all in one SQL Statement like:

INSERT INTO [TableName] (some_row) SELECT (some_row) FROM [tablename_2]

I can select and insert into any type of database now, I have all the jdbc drivers working, but need two different drivers to work at the same time.

Any Ideas?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29261
    
140

Des,
Welcome to JavaRanch!

I'm surprised that works in VB. JDBC just passed the SQL statement to the database. And you wouldn't be able to access two databases from a SQL command line.

Why do you need one statement? You can retrieve the data to Java and then insert it. As long as this is done in an XA transaction, you have rollback support.

Alternatively, you can do a database export and import in an operating system script.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Des Barron
Greenhorn

Joined: May 02, 2005
Posts: 3
If I return my resultset (from the AS400) to java and it contains 1000 rows, then I would have to run 1000 insert statements (to insert into SQL). Third party software such as DTS for SQL Server can do it in one statement going from AS400 to SQL. I haven't been able to get it to work with jdbc and from the sounds of it, it isn't possible.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Des Barron:
If I return my resultset (from the AS400) to java and it contains 1000 rows, then I would have to run 1000 insert statements (to insert into SQL).


Des,
you can go for PreparedStatement.addbatch() to bind data of multiple rows and then in one execution you can insert 1000 records to target database.provided you target database supports this feature.I have done this in oracle without any problem.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Des Barron
Greenhorn

Joined: May 02, 2005
Posts: 3
I tried the suggestion to use addBatch(), but unless I am doing it incorrectly, it still requires me to scroll through the ResultSet using while(rs.next()) to add all the results to the batch. It is the scrolling through the resultset that is very time consuming. I have played with CachedRowSet as well but I don't think it will decrease the amount of time it takes to scroll through each result. Am I out of options for trying to do this using jdbc?
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

could you put your code here, so that we can think more about problem ?

Shailesh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: transfer data from one DB to another
 
Similar Threads
How to get a blank ResultSet to do update?
best programming practises of JDBC
SQL scripts for data migration
Connecting to multiple databases using different jdbc drivers
How to store & retrieve a video file from database?