Win a copy of Terraform in Action this week in the Cloud forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Importing/copying a Table from one sqlserver to another and both present in different Servers

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

Can anyone help me any simple way to import data from table from one sqlserver database to another. It can be done in storing results in one list using result using one source db connection and copying the db data by iterating list and performing insert operations in another(destination) db using destination connection. But that would be messy. So is there any simple way to do it.


Thanks all in ADVANCE!
 
Rancher
Posts: 43027
76
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please don't post in all uppercase letters. It's considered shouting, and thus rude, all over the 'net: KeepItDown. It's also unnecessarily hard to read. You can edit your post to fix this by clicking the button in your post.
 
Bartender
Posts: 322
24
Eclipse IDE Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi @Siddharth,

This is a situation where the tools of SQL Server are better suited than JDBC. If you need something complete (such as triggers, constraints), your best solution would be to have the Database Administrator import/export the table using SQL Server Management Studio's "Import Data" task. Right Click on the database name in Management Studio and you will find those options under "task".

If you just want to move the data using JDBC (no definitions, indexes, constraints, triggers, etc), first create the new database and a matching table in the NewDatabase.

Then execute the following SQL statement:

INSERT INTO NewServer.NewDatabase..NewTable
SELECT * FROM OldServer.OldDatabase..OldTable

For more on the SQL Server syntax, please review: http://technet.microsoft.com/en-us/library/ms177563.aspx

I recommend you disable autocommit before hand so you can rollback the transfer if the two tables don't match up.

Cheers!
Chris
 
Siddharth Polisiti
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Chris ! Worked around by storing the column data in the bean and iterating them in a generic list and insert using insert query through java.
reply
    Bookmark Topic Watch Topic
  • New Topic