How to copy new records and updated records from a database table in local server to database table of remote server

Question

How to copy new records and updated records from a database table in local server to database table of remote server

ramachandran narayanan

SSC Enthusiast

Points: 184
More actions
June 6, 2013 at 11:25 pm

#294295

Hi
I am having locla server and remote server.
In the local server ,i have database table which is inserted,updated regularly.There is no primary key in the table
.Once in a week i want to copy the differential data to be updated in table of remote server.
Both table structures are same.
Please help me to find the solution.
Thanks
Chandran.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

olaf.dedig SSC Enthusiast Points: 126 More actions · Answer 1

The problem here is going to be the fact that you do not have a primary key, or a column that can uniquely identify a row, so doing a diff copy will be hard as row data can be duplicated, to make things easier try to add a unique identifier to your table, if that is a single column or a combination of columns.

Secondly how are these two servers linked if at all ? are they on a gigbit network connection ?

how much data is in the tables ?

is the time when the sync happens very important ?

if these two servers are on separate networks or are only connected via a low link export to a file and import of a file might be a option. but that is just an idea.

I would use, if possible linked servers to move the data, however without having a identifier it will be hard to get the correct data. perhaps if you had a inserted on date time value you could use that ? - get the max value of the date time column in the destination then do a select from the source table where the date is larger than the destination.

ramachandran narayanan SSC Enthusiast Points: 184 More actions · Answer 2

Hi

Thanks for reply

the rows of table will be of 10000 rows.

is it possible to take differential backup and load it in remote server.

Can I do it using C#.

I don't have idea about identifying rows using date time value .If it happens

how to differentiate between insert and update rows.

Both servers are in seperate network.

Thanks

Chandran

olaf.dedig SSC Enthusiast Points: 126 More actions · Answer 3

You could do a scheduled backup, but that will be on a whole database and might be overkill.

on separate networks (no direct connection between them) and for only 10000 rows, it might be a simple matter of exporting the whole table data to a csv or tabe delimited text file. using either a sql script using BCP or perhaps a SSIS package. then using other methods to move the file to the destination server using ftp / unc path copy etc. then truncate the destination table and then import the source file, 10 000 rows should be rather quick and considering that it only needs to happen once a week should do fine.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

Try this one..

You will get your solution for sure

http://www.sqlservercentral.com/articles/EDW/77100/

Thanks.

Let me know your results..