Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Records comparsion

    Hi,

    I have a table of say 5 columns containing 100 records. The records are extracted from DB2. Now records need to be fetched on a nightly basis to the table. Only those records that are new should be fetched. If the records are the same then do not fetch them. What is the simplest way of implementing this?

    Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    454

    set a flag

    You may add a column to set a flag to inducate which record is new.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Set up a second work table that looks like the first....

    You'll then have to deal with 3 potential actions,

    INSERT: New records on the file
    DELETES: Records that don't exists
    UPDATES: Records that are on the file, but attributes have changed

    Do you have a PK on the table now?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Posts
    48
    For most cases, datetime field is a good candidate to determine a record is old or new.
    Shianmiin

  5. #5
    Join Date
    Sep 2003
    Posts
    176
    Yes I have a primary key (It's a composite key made up of 2 columns)
    []
    QUOTE]Originally posted by Brett Kaiser
    Set up a second work table that looks like the first....

    You'll then have to deal with 3 potential actions,

    INSERT: New records on the file
    DELETES: Records that don't exists
    UPDATES: Records that are on the file, but attributes have changed

    Do you have a PK on the table now?
    [/QUOTE]

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    You only insert records that do not already exist - Could you be more specific as to what you need ? Also, what are you using now to import the records from db2 ?

  7. #7
    Join Date
    Sep 2003
    Posts
    176
    I use ODBC driver to get records from DB2. The records have a PK and attributes. Say attribute1,2,3...5. Now I only need to call insert a record if the PK has changed OR if attribute 1 for the records has changed. If attribute 2,3,4,5 have changed, then that does not matter. I do not call that records New.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    What programming language are you using ?

  9. #9
    Join Date
    Sep 2003
    Posts
    176
    T-SQL.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ummm, don't you want to INSERT when the PK changes, but UPDATE when column 1 changes? Otherwise, you get relational integrity issues. This should be handled in two different steps after the data is loaded into a staging table.

    blindman

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He wants this...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (
    		  Col1 char(1)
    		, Col2 char(1)
    		, Col3 char(1)
    		, Col4 char(1)
    		, Col5 char(1)
    		, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))
    
    CREATE TABLE myTable00 (
    		  Col1 char(1)
    		, Col2 char(1)
    		, Col3 char(1)
    		, Col4 char(1)
    		, Col5 char(1)
    		, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL
    SELECT '1','2','d','e','f' UNION ALL
    SELECT '1','3','g','h','i' UNION ALL
    SELECT '1','4','j','k','l'
    						--DELETED
    
    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL		--NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL		-- UPDATE	
    SELECT '1','3','g','h','i' UNION ALL		--NO CHANGE
    SELECT '2','3','a','b','c'			--INSERT
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO
    
    --DO DELETES FIRST
    
        DELETE FROM a
          FROM myTable99 a
     LEFT JOIN myTable00 b
    	ON a.Col1 = b.Col1
           AND a.Col2 = b.Col2
         WHERE b.Col1 IS NULL AND b.Col2 IS NULL
    
    -- INSERT
    
    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
          FROM myTable00 a
     LEFT JOIN myTable99 b
    	ON a.Col1 = b.Col1
           AND a.Col2 = b.Col2
         WHERE b.Col1 IS NULL AND b.Col2 IS NULL
    
    -- UPDATE
    
        UPDATE    a
           SET    Col3 = b.Col3
    	    , Col4 = b.Col4
    	    , Col5 = b.Col5
          FROM    myTable99 a
    INNER JOIN    myTable00 b
    	ON    a.Col1 = b.Col1
           AND    a.Col2 = b.Col2
           AND (	   a.Col3 <> b.Col3
    		OR a.Col4 <> b.Col4
    		OR a.Col5 <> b.Col5)
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2003
    Posts
    176
    Brett . Thanks a lot. I am getting close. But this is how I need it.

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL

    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','d','z' UNION ALL --NO CHANGE

    Col3,4,5 are attributes. If PK changes then insert new record. If for same PK, attribute 2,3 changes but attribute 1 is unchanged then do not INSERT (No change). I need a history of the records and so I do not do updates. I just insert new records.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...I wouldn't do ot this way...

    I wouldn't keep all the history in 1 table...I would use a trigger and a history table...

    Hey, but they're the reqs...you're going to have to find the PK with the max timestamp every time for the current row..

    EDIT: And what, prey tell, does the concept of "DELETE" mean anymore to this process?



    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (
    		  Col1 char(1)
    		, Col2 char(1)
    		, Col3 char(1)
    		, Col4 char(1)
    		, Col5 char(1)
    		, Col6 datetime DEFAULT GetDate()
    		, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2,col6))
    
    CREATE TABLE myTable00 (
    		  Col1 char(1)
    		, Col2 char(1)
    		, Col3 char(1)
    		, Col4 char(1)
    		, Col5 char(1))
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL
    SELECT '1','2','d','e','f' UNION ALL
    SELECT '1','3','g','h','i' UNION ALL
    SELECT '1','4','j','k','l'
    						--DELETED
    
    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL		--NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL		-- UPDATE	
    SELECT '1','3','g','h','i' UNION ALL		--NO CHANGE
    SELECT '2','3','a','b','c'			--INSERT
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO
    
    -- INSERT
    
    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
          FROM myTable00 a
     LEFT JOIN myTable99 b
    	ON a.Col1 = b.Col1
           AND a.Col2 = b.Col2
         WHERE b.Col1 IS NULL AND b.Col2 IS NULL
    
    -- "UPDATE"
    
    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT b.Col1, b.Col2, b.Col3, b.Col4, b.Col5
          FROM    myTable99 a
    INNER JOIN    myTable00 b
    	ON    a.Col1 = b.Col1
           AND    a.Col2 = b.Col2
           AND (	   a.Col3 <> b.Col3
    		OR a.Col4 <> b.Col4
    		OR a.Col5 <> b.Col5)
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and what, prey tell, does the concept of "PRIMARY KEY" mean anymore to this process?

    blindman

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey..it got extended to include the "property" (if you will) of time...

    smoke'em if you got 'em....

    I still think you should go with a historical table and triggers though...If you stll need to see all of the history, just create a view...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •