DTS - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

DTS

Author

Topic

SQL_Deepak
Starting Member

9 Posts

Posted - 2008-05-21 : 01:23:42

Hi DBA experts,

I have sql server 2000 database running on two machines.

How to use DTS to transfer differential data from Source database to destination database.

Thanx..

Deepak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 01:31:33

Create two connection mangers for source and destination. Add a data transformation task to transfer the data from source to a staging table. Add a Execute SQL task to add only the differential data to destination

the query used will be something like

--In staging not in dest;new records so insert
INSERT INTO Destination
SELECT fields
FROM Staging s
LEFT JOIN Destination d
ON d.PKCol=s.PKCol
WHERE d.PKCol IS NULL

--In both staging & dest;existing records so update
UPDATE d
SET d.field1=s.field1,
d.field2=s.field2,....
FROM Destination d
INNER JOIN Staging s
ON s.PKCol=d.PKCol

--In dest not in staging;records deleted from source so delete
DELETE d
FROM Destination d
LEFT JOIN Staging s
ON s.PKCol=d.PKCol
WHERE s.PKCol IS NULL

SQL_Deepak
Starting Member

9 Posts

Posted - 2008-05-21 : 20:28:09

Hi Visakh

This is ok but my Source and destination are on different servers.

Please help.

Thanx...

quote:
Originally posted by visakh16

Create two connection mangers for source and destination. Add a data transformation task to transfer the data from source to a staging table. Add a Execute SQL task to add only the differential data to destination

the query used will be something like

--In staging not in dest;new records so insert
INSERT INTO Destination
SELECT fields
FROM Staging s
LEFT JOIN Destination d
ON d.PKCol=s.PKCol
WHERE d.PKCol IS NULL

--In both staging & dest;existing records so update
UPDATE d
SET d.field1=s.field1,
d.field2=s.field2,....
FROM Destination d
INNER JOIN Staging s
ON s.PKCol=d.PKCol

--In dest not in staging;records deleted from source so delete
DELETE d
FROM Destination d
LEFT JOIN Staging s
ON s.PKCol=d.PKCol
WHERE s.PKCol IS NULL

Deepak

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-21 : 20:34:26

Thats why he said
Create two connection mangers for source and destination.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources