Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

transfer data from one DB to another

 
Des Barron
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Des Barron
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Des Barron
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
could you put your code here, so that we can think more about problem ?

Shailesh
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic