• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

copy data from one table on server1 to another table of server2

 
anvi kon
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is any one can help me ? How to copy the data from one table on server1 to another table of server2?

How to query that?

Is it possible in java?

Thanks



 
Peter Johnson
author
Bartender
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, it is possible in Java, but your database should come with a utility that can do that for you. What database are you using?
 
anvi kon
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using Mysql database.


How it is possible?

Can you please send me with simple example?

thanks
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anvi,
See the dump/export documentation for an example. You will need to use a command line (Runtime.exec) rather than JDBC.
 
anvi kon
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, for your reply.

But I want to do it though java code or query/storedprocedure automatically.

Is it possible?

thanks
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sure it's possible. Using ordinary JDBC too. Just write the query which selects the first table, read the data, and insert each record into the second table.

However the MySQL dump technique will be better in numerous ways (least of which is that you don't need to write any code).
 
anvi kon
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How would I write a query to copy a table from server1 to another table on server2?


Could you please give me one example in java using JDBC?

thanks
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Anvi,

Could you please give me one example in java using JDBC?


A possible solution is:
In java:

Preparation part:


Copy part:


Closure part



If you do not have auto commit, you will have to commit the inserts.
If there are only few records, you can commit at the end. Else you will have to commit at regular intervals during the loop.

Regards, Jan
 
Bavo Van Achte
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could anyone tell me what to do when there is already data in the destination database that has to be updated.
You can't use the INSERT command to overwrite it, right?
 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am trying to import records from one database to another database locally when a user presses a JButton. I followed the solution provided by Jan and everthing executes fine, however, it is only inserting one record into the second database and then does not execute after that and I get a SQLException:General Error. I am not sure that I am looping thru the result set correctly.
Here is what I wrote based on Jan's suggestion.
 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got it working. Now I am trying to figure out how to insert a new record if it does not exist and update the record if it does exist. I am very close to getting it figured out. I can feel it.
 
Carlo Moore
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not quite sure what the error means, but to me it seems there is a lot happening in one loop. Would it be possible to gather data first, then add your data to the next table.
If need be you can select your data in batches.
 
k Ratliff
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thats pretty much what I did to get it to execute. I just started looking into batches and that seems like the proper course of action for my program. Thanks for the tip.
 
Abdoul Kader Soumahoro
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
k Ratliff wrote:Hello,

I am trying to import records from one database to another database locally when a user presses a JButton. I followed the solution provided by Jan and everthing executes fine, however, it is only inserting one record into the second database and then does not execute after that and I get a SQLException:General Error. I am not sure that I am looping thru the result set correctly.
Here is what I wrote based on Jan's suggestion.


what if you have 50 or more columns how would you do it?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd do it the same way regardless of the number of columns. If the table has fifty columns, I need to handle fifty columns. There isn't a way to read or set columns 'in bulk' in JDBC.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic