Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Basic Table update/append question

    I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.

    In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.

    In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.
    Thanks,
    Bill

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In SQL Server (or in Access...) you are better off using a LEFT OUTER JOIN against your destination table, and filtering where the destination table key is null:

    select [YourFields]....
    from SourceTable
    left outer join DestinationTable on SourceTable.PKey = DestinationTable.PKey
    where Destination.PKey is null

    You could also use NOT EXISTS, but I prefer this method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    139
    Thanks, that worked really well!
    Thanks,
    Bill

Posting Permissions

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