Post reply

Compare Tables Then Copy New Records

  • November 30, 2005 at 2:51 am

    #61635

    Hi,

    I am trying to compare two table....

    Table A is a copy of table B.

    New records are entered into table A then copied to table B using DTS package. 

    Simple..

    What I am trying to do is only copy the new records from table A to table B. Therefore if it already exists in table B do not copy the data over.

    I can't find anything in the DTS section that will allow that, onlt replace or append.

    Can anyone point me in the right direction.

    Thanks

    Jason

     

     

     

     

  • November 30, 2005 at 5:41 am

    #606677

    Investigate the data driven query task.

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

  • November 30, 2005 at 7:42 am

    #606731

    Hi,

    In order to do that, we use a Data Pump Task where the source is an SQL Query of the form:

    Select * from tableA a

    WHERE a.key NOT IN (select b.Key from tableB b)

    The target would be tableB and the transformations are straigth forward.

     

     

    Regards,

    Andrés D'Elia

  • November 30, 2005 at 8:00 am

    #606741

    I find it much easier to just build my queries in QA...you can try testing it in QA first and then pasting it in your DTS query box...my sql usually looks like this:

    SET IDENTITY_INSERT tblB ON

INSERT INTO tblB(colID, col1, col2, col3, col4, col5)
SELECT  colID, col1, col2, col3, col4, col5 
FROM    tblA 
WHERE   colID NOT IN
(SELECT colID FROM tblB)

SET IDENTITY_INSERT tblB OFF







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 30, 2005 at 10:18 am

    #606809

    Great.

    Thanks Guys.

    All sorted now.

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

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