SQLServer procedure to select changes between two tables based on a hash field, then update (merge) the second table with the changes. Change ALTER to CREATE/DROP to create or delete the procedure. Note: The PI JDBC Adapter does not like any blank lines in the Procedure!

select_changes_then_merge.sql

alter procedure PersonDelta
as
  SET NOCOUNT ON;
  /* temp table - fill with changes between tables based on hash field */
  declare @rows table(name nvarchar(255), street nvarchar(255), city nvarchar(255), postcode nvarchar(255), state nvarchar(255), hash nvarchar(255));
  insert into @rows (name, street, city, postcode, state, hash)
    select p.name, p.street, p.city, p.postcode, p.state, p.hash from T_PERSON as p left outer join T_PERSON_HIST as h on p.name = h.name where p.hash <> isnull(h.hash,'-1');
  /* merge the cahnges into the second table */
  merge into T_PERSON_HIST as target using @rows as source
    on target.name = source.name
    when matched then
      update set target.hash = source.hash
    when not matched then
      insert (name, hash) values (source.name, source.hash);
  /* return the temp table as the resultset */
  select * from @rows;