Post reply

Copy only non existing rows

  • 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?

     

     

  • September 10, 2004 at 2:28 am

    #522462

    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!

  • September 10, 2004 at 2:34 am

    #522465

    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?

     

  • September 10, 2004 at 4:23 am

    #522502

    Hi,

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

    Pete

  • September 10, 2004 at 4:36 am

    #522506

    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.

     

  • September 10, 2004 at 2:18 pm

    #522632

    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

    Greg

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

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