0

I want to export data from A table and import the data to B table. A and B table are the same tables and they are have 100 columns. How can I export and import within JDBC? I want to do it dynamically. I do not want to write one column to other.(2 tables have same columns. But Table A in oracle and Table B in mysql) Thank you.

CompEng
  • 7,161
  • 16
  • 68
  • 122
  • Quote : "_A and B table are the same tables_", what does that mean ? Otherwise, you can consider creating a java code to retrieve data from a table and insert it into the other table, that will be quite easy in my guess. – Akheloes Jun 18 '13 at 21:39
  • @Gloserio I mean they have same columns. And I want to export data and insert other table. (bulk copy) – CompEng Jun 18 '13 at 21:40
  • Have you thought about using some java code (DataSource, Connection, Statement and ResultSet objects) ? Or you looking for an immidiate approach ? – Akheloes Jun 18 '13 at 21:43
  • I use Connection, Statement and ResultSet objects in jdbc. – CompEng Jun 18 '13 at 21:45
  • Then please do show the code, if it's mistaken or disfunctionnal indicate how so to receive better pointed help. I think the guys are misunderstanding your question. – Akheloes Jun 18 '13 at 21:47
  • @Gloserio I have 2 different connections.the issue is I dont know how can I select * from A(oracle connection with jdbc) to insert data B table (mysql connection) – CompEng Jun 18 '13 at 21:53
  • Well, I am not sure I am getting correctly the point here, but if it's only a matter of accessing the A table in Oracle via a JDBC connection then that's not far from `OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:oci); ods.setUser("scott"); ods.setPassword("tiger"); Connection conn = ods.getConnection();` – Akheloes Jun 18 '13 at 21:59
  • I can reach each table and I write select * from A; and I get data. But How can I write that result set the other table ? – CompEng Jun 18 '13 at 22:01
  • Ok, if the issue is writing down data from one to the other, then you must loop on the "oracle" resultset, retrieve a line and use the "mysql" resultSet to insert data in the B table. – Akheloes Jun 18 '13 at 22:04
  • yep that is the issue. I dont want to write each column. I want to write like insert Table A select resultset. I mean I dont want to write resultset.getstring(1), resultset.getstring(2).. ..,resultset.getstring(100) – CompEng Jun 18 '13 at 22:08
  • Yes, but you still can use `ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); for (int i = 1; i < columnCount + 1; i++) { String column = rsmd.getColumnName(i); }` which can get the names of the columns in your A table, this way you'll not have to write down all their names. All you'll have left to do is use this to generate your `select from` String. – Akheloes Jun 18 '13 at 22:14
  • okey thank you @Gloserio I try it and I will update you as soon as possible. – CompEng Jun 18 '13 at 22:16
  • Posted something, I hope that helps. – Akheloes Jun 18 '13 at 22:30
  • i think this is better as a SQL and database effort than trying to code something in java. dblinks are the way to go. – Randy Jun 19 '13 at 00:16
  • yes it s working but if I insert 1 million rows it will be very long time and bad performance. How can I insert bulk copy using jdbc? – CompEng Jun 19 '13 at 04:57

2 Answers2

1

Try:

insert into tableB
select * from tableA

This is possible also if the tables are in different databases, creating a DB-link between the databases (granted you have the permissions to do so).

You can otherwise copy a max number of columns from TableA in memory and than insert them into the TableB, but I strongly discourage this.

Unfortunately in java there is nothing similar to the .NET BulkCopy

Paolof76
  • 889
  • 1
  • 9
  • 23
  • I am using jdbc and table A and table B is in different connection. – CompEng Jun 18 '13 at 21:43
  • Oh now I see, so you'd like something like bulkcopy in .NET... what sql vendor are you using? – Paolof76 Jun 18 '13 at 21:47
  • I use java (netbeans project) with jdbc connections – CompEng Jun 18 '13 at 21:54
  • 1
    I mean, you use MySql, Oracle, SQL Server? Anyway, I suggested in my response to use a dblink. If you have not more than thousands of rows you can also copy data in memory and tranfer them into the other table. – Paolof76 Jun 18 '13 at 22:00
0

This might be of help : 

ResultSet rs = st.executeQuery("SELECT * FROM A");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

String insert_string = "INSERT INTO B(" ;
for (int i = 1; i < columnCount + 1; i++) {
     String column = rsmd.getColumnName(i);
     insert_string += column + ", " ;
     }
insert_string += " )"; // Column part of INSERT INTO B should be well formed

insert_string += " VALUES (" ;

int i=0;
while(i < columnCount - 1){
    i++;
    insert_string += "'"+ rs.getString(i)+"', " ;
}

insert_string += "'" + rs.getString(columnCount) + ")" ; // VALUES part should be ok by now

This far, we must have a one valid INSERT statement, but that's only for one row in the rs object. An iteration with rs.next() must be included in the code so as to repead the creation of that INSERT string for all A rows.

As for the performance, I honestly have no clue. I don't recommend this, but I think it's a fair way of addressing the question.

Akheloes
  • 1,352
  • 3
  • 11
  • 28
  • yes you are right if I insert 1 million rows it will be very long time and bad performance. How can I insert bulk copy using jdbc? – CompEng Jun 19 '13 at 04:55
  • 1
    Yes, that'll quickly run you in a out of memory sort of exception ! Check out this [link](http://viralpatel.net/blogs/batch-insert-in-java-jdbc/), the last part "batch withing batch" may help you. Still, in the internet it seems that there's no equivalent to bulk insert with JDBC ; hence - and until further notice - , I think you have to suffer to do this programmatically lol. – Akheloes Jun 19 '13 at 08:03
  • Check out this as well : [here](http://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc) – Akheloes Jun 19 '13 at 08:05
  • when I write stmt.executeQuery(insert_string ) ; it returns = A Result Set was expected but not generated from query ; – CompEng Jun 19 '13 at 08:44
  • Have you used `ResultSet rs = stmt.executeQuery(insert_string)` ? – Akheloes Jun 19 '13 at 08:51
  • that is not working. stmt.addBatch(insert_string); is working – CompEng Jun 19 '13 at 10:59
  • Cool, did you finally succeed ? – Akheloes Jun 19 '13 at 11:18
  • I can insert data but it is still slow I do not understand.Because I made batches include 500 insert statament and it inserts 190 rows per second – CompEng Jun 20 '13 at 06:29
  • So now it works but it's slow ? If that's the case, I think it deserve a question on its own since the issue would clearly be performance, tag that question with DB tags, so people from DB background can help you increase performance. – Akheloes Jun 20 '13 at 20:54