Looking for missing records

  • Steve Johnson

    Ten Centuries

    Points: 1294

    We copy tables from our production database which is a Progress DB into a SQL 2k db. Nightly on the SQL side we either truncate the table and reload the whole table or we will do an incremental update to the table. We have a table that has 14,000,000 records and is missing about 1500 records on the SQL side. I am looking for some ideas how I could compare the primary keys and find the missing records quickly.

    Steve Johnson


    Steve Johnson

  • Andy Warren

    SSC Guru

    Points: 119694

    Your best bet is to get the primary keys from both tables in one database (you can try the linked server, but may be slow). Then just do a left join, like this:

    select progress.pkey, sql.pkey from tempa as Progress left join tempb as SQL on Progress.pkey=sql.pkey where sql.pkey is null

    It row return the keys of the rows you need to add to SQL. Worth indexing before you try it:-)

    Andy

  • Steve Johnson

    Ten Centuries

    Points: 1294

    Thanks Andy that worked perfectly.... I am also trying to setup linked servers now... looks like something that could help us with other issues that we are having....:o)

    Steve Johnson


    Steve Johnson

Viewing 3 posts - 1 through 3 (of 3 total)

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