SQL Server Update from another table when record is different.

[DataMiser]

I'm trying to figure out the best way to update some data in SQL Server 2008 and 2012

I have one table that has up to date records in it consisting of 10 fields
I have another table that has the same structure but the data may be older than the other table.
Need to update table 2 with data from table 1 when the data for the given record(s) is different in the two tables.

For example both tables contain a matching key field, both tables have 4 fields that will not change over time
The other 6 fields may change but in most cases only the qty field will be different and need to be updated.

What I would like to do is compare the two records and update every record with the data from the other table where the key is the same but one or more other values are different.

Any ideas?

btw this needs to be fairly quick and needs to process near 1 million records

[techgnome]

I'd probably use a merge statement...
Here's a simple sample based on one I've used to load data into our data warehouse:

  merge DW_DIM_SAMPLETABLE as t
  using DW_DIM_SAMPLETABLE_STAGE as s
  on t.TABLESYSTEMID = s.TABLESYSTEMID
  when not matched by target
    then insert (
           TABLESYSTEMID
          ,FOREIGNKEYFACTID
          ,REFERENCE
          ,ISINCLUDED
          ,ETLCONTROLID
          ,[SOURCEDIMID]

    ) values (
            s.TABLESYSTEMID
           ,s.FOREIGNKEYFACTID
           ,s.REFERENCE
           ,s.ISINCLUDED
           ,s.ETLCONTROLID
           ,s.[SOURCEDIMID]
    )
  when matched
    then update
   SET 
      t.FOREIGNKEYFACTID  = s.FOREIGNKEYFACTID
      ,t.REFERENCE  = s.REFERENCE
      ,t.ISINCLUDED = s.ISINCLUDED
      ,t.ETLCONTROLID = s.ETLCONTROLID
      ,t.[SOURCEDIMID] = s.[SOURCEDIMID];

For more info, google "t-sql merge"
The first table following the MERGE keyword becomes the target table. The one following USING becomes source. Source doesn't have to be a table either, it can be a query, just happens in this case to be a table as I'm moving form staging to production. You then indicate what to happen when there's a match, no match in target, no match in source, and so on. the ON clause works like a join, indicates the matching fields.

-tg