• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Siddharth Polisiti
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • 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!
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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.
 
Chris Barrett
Bartender
Posts: 317
24
Eclipse IDE Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic