Copy only non existing rows

Question

Copy only non existing rows

Superchick

SSC Eights!

Points: 810
More actions
September 10, 2004 at 1:19 am

#164917

How can I set DTS up to only copy rows from my source table that doesnt already exist in the destination table?

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

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

There are a few ways to achive this. This probably the simplest.

Set up two data connections in dts. Click on source and destination and right click to get the task called 'transform data task'. There is a window to put a sql script in. Use the script below to get the data you want for the source side. Then go to the transform tab in the tansform data task and map the destination columns to the source columns generated by the script.

You can use:===========================

Insert into tblDest (col1,col2)

Select col1, col2 from tblSrc

where tblSrcJoinID not in (

Select tblDest.JoinID from tblDest)

=====================================

A more efficient way ios to join the tables with a right outer join and select the records that are null in the dest table. See BOL for right outer join.

For mor info on dts see: http://www.sqldts.com

------------------------------
The Users are always right - when I'm not wrong!

Superchick SSC Eights! Points: 810 More actions · Answer 2

Hi,

The problem is that the tables are on two seperate servers , so I cant rference the destination table from my query, in DTS.

Any Ideas?

Peter Gadsby-171567 SSC Rookie Points: 44 More actions · Answer 3

Hi,

Wouldn't it be simplier to call a stored procedure from DTS which accessed both the tables using a linked server.

Pete

Superchick SSC Eights! Points: 810 More actions · Answer 4

yes, indeed it would be. That is how I had it setup initially. But suddenly it came up with an error saying. Cannot find database ID 11.

Ive had a look, and apparently it is a known bug. It shouldve been fixed in SP3 but I am running SP 3 and still getting the error. I have backup and restore the DB but nothing works.

So I need to find an alternative. I really dont want to have to set up replication.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Jonathan's insert statement will still work if you qualify the table names with the server name and database name thusly: servername.databasename.tablename

I believe you still need to have a linked server defined.

Greg