USP_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_UPDATEFROMXML

CREATE PROCEDURE dbo.USP_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_UPDATEFROMXML 
(
@BATCHCONSTITUENTUPDATEID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)

as

set nocount on;

if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

if @CHANGEDATE is null 
    set @CHANGEDATE = getdate()

-- build a temporary table containing the values from the XML

declare @TempTbl table (
   [ADDRESSBLOCK] nvarchar(150),
   [ADDRESSID] uniqueidentifier,
   [ADDRESSTYPECODEID] uniqueidentifier,
   [CART] nvarchar(max),
   [CERTIFICATIONDATA] int,
   [CITY] nvarchar(max),
   [CONGRESSIONALDISTRICTCODEID] uniqueidentifier,
   [COUNTRYID] uniqueidentifier,
   [COUNTYCODEID] uniqueidentifier,
   [DONOTMAIL] bit,
   [DONOTMAILREASONCODEID] uniqueidentifier,
   [DPC] nvarchar(max),
   [ENDDATE] char(4),
   [HISTORICALENDDATE] date,
   [HISTORICALSTARTDATE] date,
   [ID] uniqueidentifier,
   [INFOSOURCECODEID] uniqueidentifier,
   [INFOSOURCECOMMENTS] nvarchar(256),
   [ISPRIMARY] bit,
   [LASTVALIDATIONATTEMPTDATE] date,
   [LOCALPRECINCTCODEID] uniqueidentifier,
   [LOT] nvarchar(5),
   [OMITFROMVALIDATION] bit,
   [POSTCODE] nvarchar(12),
   [REGIONCODEID] uniqueidentifier,
   [ROWORIGINCODE] tinyint,
   [SEQUENCE] int,
   [STARTDATE] char(4),
   [STATEHOUSEDISTRICTCODEID] uniqueidentifier,
   [STATEID] uniqueidentifier,
   [STATESENATEDISTRICTCODEID] uniqueidentifier,
   [UPDATEHOUSEHOLD] bit,
   [VALIDATIONMESSAGE] nvarchar(200))

insert into @TempTbl select 
    [ADDRESSBLOCK],
    [ADDRESSID],
    [ADDRESSTYPECODEID],
    [CART],
    [CERTIFICATIONDATA],
    [CITY],
    [CONGRESSIONALDISTRICTCODEID],
    [COUNTRYID],
    [COUNTYCODEID],
    [DONOTMAIL],
    [DONOTMAILREASONCODEID],
    [DPC],
    [ENDDATE],
    [HISTORICALENDDATE],
    [HISTORICALSTARTDATE],
    [ID],
    [INFOSOURCECODEID],
    [INFOSOURCECOMMENTS],
    [ISPRIMARY],
    [LASTVALIDATIONATTEMPTDATE],
    [LOCALPRECINCTCODEID],
    [LOT],
    [OMITFROMVALIDATION],
    [POSTCODE],
    [REGIONCODEID],
    [ROWORIGINCODE],
    [SEQUENCE],
    [STARTDATE],
    [STATEHOUSEDISTRICTCODEID],
    [STATEID],
    [STATESENATEDISTRICTCODEID],
    [UPDATEHOUSEHOLD],
    [VALIDATIONMESSAGE] 
from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_FROMITEMLISTXML(@XML)

update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

if @@Error <> 0
    return 1;

declare @contextCache varbinary(128);
declare @e int;

-- cache current context information 

set @contextCache = CONTEXT_INFO();

-- set CONTEXT_INFO to @CHANGEAGENTID 

if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

-- delete any items that no longer exist in the XML table

delete from dbo.[BATCHCONSTITUENTUPDATEADDRESSES] where [BATCHCONSTITUENTUPDATEADDRESSES].ID in 
    (select ID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES
    (
        @BATCHCONSTITUENTUPDATEID
    )
    EXCEPT select ID from @TempTbl)    

select @e=@@error;

-- reset CONTEXT_INFO to previous value 

if not @contextCache is null
    set CONTEXT_INFO @contextCache;

if @e <> 0
    return 2;

-- update the items that exist in the XML table and the db

update [BATCHCONSTITUENTUPDATEADDRESSES]
        set [BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSBLOCK]=temp.[ADDRESSBLOCK],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSID]=temp.[ADDRESSID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSTYPECODEID]=temp.[ADDRESSTYPECODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[CART]=temp.[CART],
        [BATCHCONSTITUENTUPDATEADDRESSES].[CERTIFICATIONDATA]=temp.[CERTIFICATIONDATA],
        [BATCHCONSTITUENTUPDATEADDRESSES].[CITY]=temp.[CITY],
        [BATCHCONSTITUENTUPDATEADDRESSES].[CONGRESSIONALDISTRICTCODEID]=temp.[CONGRESSIONALDISTRICTCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[COUNTRYID]=temp.[COUNTRYID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[COUNTYCODEID]=temp.[COUNTYCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAIL]=temp.[DONOTMAIL],
        [BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAILREASONCODEID]=temp.[DONOTMAILREASONCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[DPC]=temp.[DPC],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ENDDATE]=temp.[ENDDATE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALENDDATE]=temp.[HISTORICALENDDATE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALSTARTDATE]=temp.[HISTORICALSTARTDATE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ID]=temp.[ID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECODEID]=temp.[INFOSOURCECODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECOMMENTS]=temp.[INFOSOURCECOMMENTS],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ISPRIMARY]=temp.[ISPRIMARY],
        [BATCHCONSTITUENTUPDATEADDRESSES].[LASTVALIDATIONATTEMPTDATE]=temp.[LASTVALIDATIONATTEMPTDATE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[LOCALPRECINCTCODEID]=temp.[LOCALPRECINCTCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[LOT]=temp.[LOT],
        [BATCHCONSTITUENTUPDATEADDRESSES].[OMITFROMVALIDATION]=temp.[OMITFROMVALIDATION],
        [BATCHCONSTITUENTUPDATEADDRESSES].[POSTCODE]=temp.[POSTCODE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[REGIONCODEID]=temp.[REGIONCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[ROWORIGINCODE]=coalesce(temp.[ROWORIGINCODE], [BATCHCONSTITUENTUPDATEADDRESSES].[ROWORIGINCODE]),
        [BATCHCONSTITUENTUPDATEADDRESSES].[SEQUENCE]=temp.[SEQUENCE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[STARTDATE]=temp.[STARTDATE],
        [BATCHCONSTITUENTUPDATEADDRESSES].[STATEHOUSEDISTRICTCODEID]=temp.[STATEHOUSEDISTRICTCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[STATEID]=temp.[STATEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[STATESENATEDISTRICTCODEID]=temp.[STATESENATEDISTRICTCODEID],
        [BATCHCONSTITUENTUPDATEADDRESSES].[UPDATEHOUSEHOLD]=temp.[UPDATEHOUSEHOLD],
        [BATCHCONSTITUENTUPDATEADDRESSES].[VALIDATIONMESSAGE]=temp.[VALIDATIONMESSAGE],
        [BATCHCONSTITUENTUPDATEADDRESSES].CHANGEDBYID = @CHANGEAGENTID,
        [BATCHCONSTITUENTUPDATEADDRESSES].DATECHANGED = @CHANGEDATE

    from dbo.[BATCHCONSTITUENTUPDATEADDRESSES] inner join @TempTbl as [temp] on [BATCHCONSTITUENTUPDATEADDRESSES].ID = [temp].ID
    where ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSBLOCK]<>temp.[ADDRESSBLOCK]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSBLOCK] is null and temp.[ADDRESSBLOCK] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSBLOCK] is not null and temp.[ADDRESSBLOCK] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSID]<>temp.[ADDRESSID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSID] is null and temp.[ADDRESSID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSID] is not null and temp.[ADDRESSID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSTYPECODEID]<>temp.[ADDRESSTYPECODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSTYPECODEID] is null and temp.[ADDRESSTYPECODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ADDRESSTYPECODEID] is not null and temp.[ADDRESSTYPECODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CART]<>temp.[CART]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CART] is null and temp.[CART] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CART] is not null and temp.[CART] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CERTIFICATIONDATA]<>temp.[CERTIFICATIONDATA]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CERTIFICATIONDATA] is null and temp.[CERTIFICATIONDATA] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CERTIFICATIONDATA] is not null and temp.[CERTIFICATIONDATA] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CITY]<>temp.[CITY]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CITY] is null and temp.[CITY] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CITY] is not null and temp.[CITY] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CONGRESSIONALDISTRICTCODEID]<>temp.[CONGRESSIONALDISTRICTCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CONGRESSIONALDISTRICTCODEID] is null and temp.[CONGRESSIONALDISTRICTCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[CONGRESSIONALDISTRICTCODEID] is not null and temp.[CONGRESSIONALDISTRICTCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTRYID]<>temp.[COUNTRYID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTRYID] is null and temp.[COUNTRYID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTRYID] is not null and temp.[COUNTRYID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTYCODEID]<>temp.[COUNTYCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTYCODEID] is null and temp.[COUNTYCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[COUNTYCODEID] is not null and temp.[COUNTYCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAIL]<>temp.[DONOTMAIL]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAIL] is null and temp.[DONOTMAIL] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAIL] is not null and temp.[DONOTMAIL] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAILREASONCODEID]<>temp.[DONOTMAILREASONCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAILREASONCODEID] is null and temp.[DONOTMAILREASONCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DONOTMAILREASONCODEID] is not null and temp.[DONOTMAILREASONCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DPC]<>temp.[DPC]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DPC] is null and temp.[DPC] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[DPC] is not null and temp.[DPC] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ENDDATE]<>temp.[ENDDATE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ENDDATE] is null and temp.[ENDDATE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ENDDATE] is not null and temp.[ENDDATE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALENDDATE]<>temp.[HISTORICALENDDATE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALENDDATE] is null and temp.[HISTORICALENDDATE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALENDDATE] is not null and temp.[HISTORICALENDDATE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALSTARTDATE]<>temp.[HISTORICALSTARTDATE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALSTARTDATE] is null and temp.[HISTORICALSTARTDATE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[HISTORICALSTARTDATE] is not null and temp.[HISTORICALSTARTDATE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ID]<>temp.[ID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ID] is null and temp.[ID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ID] is not null and temp.[ID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECODEID]<>temp.[INFOSOURCECODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECODEID] is null and temp.[INFOSOURCECODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECODEID] is not null and temp.[INFOSOURCECODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECOMMENTS]<>temp.[INFOSOURCECOMMENTS]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECOMMENTS] is null and temp.[INFOSOURCECOMMENTS] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[INFOSOURCECOMMENTS] is not null and temp.[INFOSOURCECOMMENTS] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ISPRIMARY]<>temp.[ISPRIMARY]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ISPRIMARY] is null and temp.[ISPRIMARY] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ISPRIMARY] is not null and temp.[ISPRIMARY] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LASTVALIDATIONATTEMPTDATE]<>temp.[LASTVALIDATIONATTEMPTDATE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LASTVALIDATIONATTEMPTDATE] is null and temp.[LASTVALIDATIONATTEMPTDATE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LASTVALIDATIONATTEMPTDATE] is not null and temp.[LASTVALIDATIONATTEMPTDATE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOCALPRECINCTCODEID]<>temp.[LOCALPRECINCTCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOCALPRECINCTCODEID] is null and temp.[LOCALPRECINCTCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOCALPRECINCTCODEID] is not null and temp.[LOCALPRECINCTCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOT]<>temp.[LOT]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOT] is null and temp.[LOT] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[LOT] is not null and temp.[LOT] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[OMITFROMVALIDATION]<>temp.[OMITFROMVALIDATION]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[OMITFROMVALIDATION] is null and temp.[OMITFROMVALIDATION] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[OMITFROMVALIDATION] is not null and temp.[OMITFROMVALIDATION] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[POSTCODE]<>temp.[POSTCODE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[POSTCODE] is null and temp.[POSTCODE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[POSTCODE] is not null and temp.[POSTCODE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[REGIONCODEID]<>temp.[REGIONCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[REGIONCODEID] is null and temp.[REGIONCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[REGIONCODEID] is not null and temp.[REGIONCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[ROWORIGINCODE]<>coalesce(temp.[ROWORIGINCODE], [BATCHCONSTITUENTUPDATEADDRESSES].[ROWORIGINCODE])) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[SEQUENCE]<>temp.[SEQUENCE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[SEQUENCE] is not null and temp.[SEQUENCE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STARTDATE]<>temp.[STARTDATE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STARTDATE] is null and temp.[STARTDATE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STARTDATE] is not null and temp.[STARTDATE] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEHOUSEDISTRICTCODEID]<>temp.[STATEHOUSEDISTRICTCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEHOUSEDISTRICTCODEID] is null and temp.[STATEHOUSEDISTRICTCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEHOUSEDISTRICTCODEID] is not null and temp.[STATEHOUSEDISTRICTCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEID]<>temp.[STATEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEID] is null and temp.[STATEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATEID] is not null and temp.[STATEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATESENATEDISTRICTCODEID]<>temp.[STATESENATEDISTRICTCODEID]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATESENATEDISTRICTCODEID] is null and temp.[STATESENATEDISTRICTCODEID] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[STATESENATEDISTRICTCODEID] is not null and temp.[STATESENATEDISTRICTCODEID] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[UPDATEHOUSEHOLD]<>temp.[UPDATEHOUSEHOLD]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[UPDATEHOUSEHOLD] is null and temp.[UPDATEHOUSEHOLD] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[UPDATEHOUSEHOLD] is not null and temp.[UPDATEHOUSEHOLD] is null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[VALIDATIONMESSAGE]<>temp.[VALIDATIONMESSAGE]) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[VALIDATIONMESSAGE] is null and temp.[VALIDATIONMESSAGE] is not null) or 
        ([BATCHCONSTITUENTUPDATEADDRESSES].[VALIDATIONMESSAGE] is not null and temp.[VALIDATIONMESSAGE] is null)

if @@Error <> 0
    return 3;    

-- insert new items

insert into [BATCHCONSTITUENTUPDATEADDRESSES] 
    ([BATCHCONSTITUENTUPDATEID], 
    [ADDRESSBLOCK],
    [ADDRESSID],
    [ADDRESSTYPECODEID],
    [CART],
    [CERTIFICATIONDATA],
    [CITY],
    [CONGRESSIONALDISTRICTCODEID],
    [COUNTRYID],
    [COUNTYCODEID],
    [DONOTMAIL],
    [DONOTMAILREASONCODEID],
    [DPC],
    [ENDDATE],
    [HISTORICALENDDATE],
    [HISTORICALSTARTDATE],
    [ID],
    [INFOSOURCECODEID],
    [INFOSOURCECOMMENTS],
    [ISPRIMARY],
    [LASTVALIDATIONATTEMPTDATE],
    [LOCALPRECINCTCODEID],
    [LOT],
    [OMITFROMVALIDATION],
    [POSTCODE],
    [REGIONCODEID],
    [ROWORIGINCODE],
    [SEQUENCE],
    [STARTDATE],
    [STATEHOUSEDISTRICTCODEID],
    [STATEID],
    [STATESENATEDISTRICTCODEID],
    [UPDATEHOUSEHOLD],
    [VALIDATIONMESSAGE],                
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select @BATCHCONSTITUENTUPDATEID, 
    [ADDRESSBLOCK],
    [ADDRESSID],
    [ADDRESSTYPECODEID],
    [CART],
    [CERTIFICATIONDATA],
    [CITY],
    [CONGRESSIONALDISTRICTCODEID],
    [COUNTRYID],
    [COUNTYCODEID],
    [DONOTMAIL],
    [DONOTMAILREASONCODEID],
    [DPC],
    [ENDDATE],
    [HISTORICALENDDATE],
    [HISTORICALSTARTDATE],
    [ID],
    [INFOSOURCECODEID],
    [INFOSOURCECOMMENTS],
    [ISPRIMARY],
    [LASTVALIDATIONATTEMPTDATE],
    [LOCALPRECINCTCODEID],
    [LOT],
    [OMITFROMVALIDATION],
    [POSTCODE],
    [REGIONCODEID],
    coalesce([ROWORIGINCODE], cast(0 as tinyint)) as [ROWORIGINCODE],
    [SEQUENCE],
    [STARTDATE],
    [STATEHOUSEDISTRICTCODEID],
    [STATEID],
    [STATESENATEDISTRICTCODEID],
    [UPDATEHOUSEHOLD],
    [VALIDATIONMESSAGE], 
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[BATCHCONSTITUENTUPDATEADDRESSES] as data where data.ID = [temp].ID)

if @@Error <> 0
    return 4;

return 0;