Table Synchronization Script

Question

Post reply

Table Synchronization Script

hungvo

SSC Rookie

Points: 42
More actions
November 16, 2007 at 7:16 am

#126995

Hi All,
I would like to synchronize a table to another table on different server by script. For example, tableSource, databaseSource, serverSource to tableDestination, databaseDestination, serverDestination.
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Oana Schuster SSCommitted Points: 1642 More actions · Answer 1

Hello,

there are more ways to do this.

what i would do is to copy the source table on the destination table using DTS.

after that you can insert the missing data into the destinationtable like this:

insert into destinationtable

(list of columns)

SELECT

(list of columns)

from sourcetable

left outer join destinationtable on destinationtable.id = sourcetable.id

where

destinationtable.id is null

id -- is a fictive column that i used for the join condition

i'm not sure if this is what you are asking

Oana.

hungvo SSC Rookie Points: 42 More actions · Answer 2

Oana Schuster (11/16/2007)
Hello,
there are more ways to do this.
what i would do is to copy the source table on the destination table using DTS.
after that you can insert the missing data into the destinationtable like this:
insert into destinationtable
(list of columns)
SELECT
(list of columns)
from sourcetable
left outer join destinationtable on destinationtable.id = sourcetable.id
where
destinationtable.id is null
id -- is a fictive column that i used for the join condition
i'm not sure if this is what you are asking
Oana.

Thanks Oana,

I want that completely by commands only since our server cannot use other interfaces just today.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Would you please clarify what you mean by strictly by command? I think I know, but I would like to be sure before going further.

Also, know the DDL (including PK's) of the tables would help.

hungvo SSC Rookie Points: 42 More actions · Answer 4

Lynn Pettis (11/16/2007)
Would you please clarify what you mean by strictly by command? I think I know, but I would like to be sure before going further.

I mean commands are in a bat file and run the bat file at a specific time.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442467

November 16, 2007 at 10:32 am

#751911

What have you come up with so far?

hungvo SSC Rookie Points: 42 More actions · Answer 6

Lynn Pettis (11/16/2007)
What have you come up with so far?

I am playing on http://msdn2.microsoft.com/en-us/library/ms162843.aspx