"Synch" table one SQL server to another via DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

"Synch" table one SQL server to another via DTS

Author

Topic

rspinell
Starting Member

36 Posts

Posted - 2008-01-18 : 11:11:10

I was hoping someone could help me with a question. I have a server that has a table with 5.6 million rows of data. I want to use DTS to transfer all the rows in the table from one SQL server to another via DTS, which I have got to work correctly. The problem I'm having is when I run the DTS job the 2nd time, it does all 5.6 million rows again and not just the deltas since the last DTS job. Is there anyway to use DTS to transfer the deltas (ex: 1000 rows since the last DTS job) from server 1 to server 2 ?

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 11:29:35

One method of implementing this is to maintain a log table in source db which is populated by dts each time with current datetime. If you have audit columns like datecreated/lastupdated dates in your source table each time dts will pick up only those records with datecreated/lastupdated > max(date) from log table. This will ensure picking up records after last dts run alone which can be then transferred to destn db. You may use a variable in dts to get max(date) from log table and use this in transform data task query to get delta records.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-20 : 20:39:07

You can run sql statement in dts package to copy over rows not in target table.

rspinell
Starting Member

36 Posts

Posted - 2008-01-21 : 00:26:34

quote:
Originally posted by rmiao

You can run sql statement in dts package to copy over rows not in target table.

How would I got about doing that ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-21 : 00:52:42

quote:
Originally posted by rspinell

quote:
Originally posted by rmiao

You can run sql statement in dts package to copy over rows not in target table.

How would I got about doing that ?

Thanks

SELECT * FROM SourceTable st
LEFT OUTER JOIN TargetTable tt
ON st.PKCol=tt.PKCol
WHERE tt.PKCol is null

this gives records in source not in destn (new records)

SELECT * FROM TargetTable tt
LEFT OUTER JOIN SourceTable st
ON st.PKCol=tt.PKCol
WHERE st.PKCol is null

this gives records in destination but not in source (deleted records from source)

SELECT * FROM TargetTable tt
INNER JOIN SourceTable st
ON st.PKCol=tt.PKCol
WHERE st.DateModified > tt.DateModified

gives records in source updated since its brought to destination

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 00:56:39

Create linked server to remote sql instance, then run code like:

insert into linked_server.db.schema.table select * from table where pkey not in (select pkey from linked_server.db.schema.table)

rspinell
Starting Member

36 Posts

Posted - 2008-01-22 : 18:17:11

Guys, thanks for this info. I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources