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...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.|||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...|||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.)|||You can use stamp.
put a stamp field that always be updated when the data updated.|||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)|||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).