Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61

    Unanswered: Database Compare / Update

    Ok, I have 2 Tables,

    table1

    ID, Count

    table2

    ID, Count,.....

    I need to go through table1, and make a change to the table2.count, if the table1.count is != table2.count

    Are Cursors the only way to accomplish this?

    Thanks alot guys/gals...

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Database Compare / Update

    Q1 Are Cursors the only way to accomplish this?
    A1 No.

    If the requirement is simply to have a similar count record maintained in a second table for a given ID; a common approach would probably involve an Update trigger (and possibly an Insert trigger and a Delete trigger as well), depending on the exact business rules.

  3. #3
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    table 2 is actually a live table with data maintained thru regular channels. Once a day (at night) we get data from an external source with changes. One of these changes is an updated count (count of pictures on file) for each ID. (not all records have changes).

    So I need to scan the live table (table 2) for matching records in the changes table (which is imported from a text file into a table for other processing), and update matching IDs with count updates.

    I am probably lousy at describing what I am after.. but thanks for the help...

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Post

    RE: So I need to scan the live table (table 2) for matching records in the changes table (which is imported from a text file into a table for other processing), and update matching IDs with count updates.
    One or more appropriate trigger(s) on the 'changes' table (or alternatively, on some subsequent processing work table) may well be able to accomplish the required updates. (Even using bcp or Bulk Insert to import the data would not perclude trigger based implementations, as both support 'FIRE TRIGGERS' type options.)

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    You can use stamp.
    put a stamp field that always be updated when the data updated.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Post

    RE:
    One or more appropriate trigger(s) on the 'changes' table (or alternatively, on some subsequent processing work table) may well be able to accomplish the required updates. (Even using bcp or Bulk Insert to import the data would not perclude trigger based implementations, as both support 'FIRE TRIGGERS' type options.)
    See the dba_tr_i_Changes_Source for a trigger stored procedure example, with supporting Live_Target and Changes_Source table ddl statements.

    Execute the: DEMO EXAMPLES separately to most clearly see the statement results.

    The purpose is to Illustrate a trigger implementation on a Changes_Source table (which is populated from an external source).

    As the triggered Changes_Source is populated over time with additional records, the related Live_Target table rows will be updated as appropriate. Additional code could handle 'new' changes_Source..PictureGroupID values (those not already present in table Live_Target)
    Attached Files Attached Files

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    For bulk loads, one may use something like the following in the body of the trigger:

    UPDATE Target
    SET
    Target.PictureCount = Source.PictureCount,
    Target.LastUpDated = Source.ChangeDateTime
    FROM
    tempdb.dbo.Live_Target Target
    INNER JOIN
    tempdb.dbo.Changes_Source Source
    ON
    Target.PictureGroupID = Source.PictureGroupID
    Where
    Source.ChangeDateTime >= Target.LastUpDated

    Then, as the triggered Changes_Source is populated by batch loads (using bcp in or bulk insert with the fire triggers options) the related Live_Target table rows will be updated as appropriate. Here too, additional code could handle 'new' Changes_Source..PictureGroupID values (those not already present in table Live_Target).

Posting Permissions

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