• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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



 
author
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 148
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using Mysql database.


How it is possible?

Can you please send me with simple example?

thanks
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 148
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 148
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Greenhorn
Posts: 6
Netbeans IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic