Compare tables and identify new or changed fields

New to SQL, would like to compare fields between a stg and src table. The identify any differences between the tables and assign transaction stat开发者_开发技巧us of 'C' for change. Any new records will be set with 'A' for add.

STG_DM_CLIENT and SRC_DM_CLIENT

What is the best way to do it, would it be best to do a some form of union all. Unsure how to proceed, any assistance welcomed.

You can identify new records by using NOT IN or NOT EXISTS

update STG_DM_CLIENT SET TransactionStatus = 'A' WHERE ID IN
     (select Id from STG_DM_CLIENT 
            where Id not in (select Id from SRC_DM_CLIENT))

Then, you can identify changed records by comparing fields:

update STG_DM_CLIENT SET TransactionStatus = 'C' WHERE ID IN
    (select STG_DM_CLIENT.Id from STG_DM_CLIENT 
        join SRC_DM_CLIENT on SRC_DM_CLIENT.Id = STG_DM_CLIENT.Id
        where (SRC_DM_CLIENT.Field1 != STG_DM_CLIENT.Field1
            OR SRC_DM_CLIENT.Field2 != STG_DM_CLIENT.Field2 ...))

update
  [STG]
set
  TransactionStatus = CASE WHEN [SRC].id IS NULL THEN 'A' ELSE 'C' END
from
  STG_DM_CLIENT  AS [STG]
left join
  SRC_DM_CLIENT  AS [SRC]
    ON [STG].id = [SRC].id -- Or whatever relates the records 1:1
WHERE
  [SRC].id IS NULL
  OR [STG].field1 <> [SRC].field1
  OR [STG].field2 <> [SRC].field2
  OR [STG].field3 <> [SRC].field3
  ...
  OR [STG].fieldn <> [SRC].fieldn