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.
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.
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.
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Joined: May 02, 2005
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?