Hi connersz,

Have you tested your query that the record exists in their tables before you update dbo.tblSalesTop20CustomersList, INSERT INTO dbo.tblSalesTop20CustomersList ? If not, please test them.

--UPDATE dbo.tblSalesTop20CustomersList
  --      SET
  --              CustomerID = data.c.value('@customerid', 'int')
  --              ,CustomerRank = data.c.value('@number', 'int')
  --              ,LastUpdatedBy = @RequestingUser
  --              ,DatetimeLastUpdated = GETDATE()

 select data.c.value('@customerid', 'int')
        ,data.c.value('@number', 'int')
        ,@RequestingUser
       , *
       FROM
                dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
                INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
                ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
        WHERE
                tblSalesTop20CustomersList.Year = @Year
                AND tblSalesTop20CustomersList.SectorID = @SectorID
                AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
 
--INSERT INTO dbo.tblSalesTop20CustomersList
        SELECT
                @Year AS Year
                ,@SectorID AS SectorID
                ,@SalesManagerID AS SalesManagerID
                ,data.c.value('@customerid', 'int') AS CustomerID
                ,data.c.value('@number', 'int') AS CustomerRank
                ,@RequestingUser
                ,GETDATE()
                ,0
        FROM
                @xmlCustomerData.nodes('/root/rank') AS data(c)
                LEFT OUTER JOIN dbo.tblSalesTop20CustomersList AS tblSalesTop20CustomersList
                        ON data.c.value('@number', 'int') = tblSalesTop20CustomersList.CustomerRank
                        AND tblSalesTop20CustomersList.Year = @Year
                        AND tblSalesTop20CustomersList.SectorID = @SectorID
                        AND tblSalesTop20CustomersList.SalesManagerID = @SalesManagerID
        WHERE NOT EXISTS
        (
        SELECT CustList.CustomerID
        FROM dbo.tblSalesTop20CustomersList As CustList
        INNER JOIN @xmlCustomerData.nodes('/root/rank') AS data(c)
        ON data.c.value('@customerid', 'int') = CustList.CustomerID
        )

Thanks.