Is a solution to identify data changes:
--==========================================================
--// Create a new Index
--==========================================================
drop table tbStockIndexTrack
go
Create table tbStockIndexTrack
( tnz_StockNumber nvarchar(100),
tnz_name nvarchar(100),
DataHash AS (hashbytes('MD5',concat(tnz_StockNumber,tnz_name))),
modifiedDateOn datetime)
Insert tbStockIndexTrack (tnz_StockNumber, tnz_name, modifiedDateOn)
select tnz_StockNumber, tnz_name, getdate() from TurnersGroup_MSCRM..tnz_stockBase
create index idxtnz_StockNumber on tbStockIndexTrack(tnz_StockNumber)
--==========================================================
--// Make any changes
--==========================================================
Update TM
Set tnz_name = 'Test' -- 1200cl Deutz Wine Bottle. Trade Me Reference: 474781057 Consignment GoodNo. 622637/25
From (
Select top 10 tnz_StockNumber,tnz_name from TurnersGroup_MSCRM..tnz_stockBase where tnz_StockNumber = 1017672
) TM
--==========================================================
--// Identify the changes
--==========================================================
with ProdBase
as
(
Select
tnz_StockNumber,
tnz_name,
DataHash = hashbytes('MD5',concat(tnz_StockNumber,tnz_name))
From TurnersGroup_MSCRM..tnz_stockBase
),
Delta
as
(
Select
tnz_StockNumber,
tnz_name,
DataHash
From tbStockIndexTrack
)
Select
top 10
p.*
From ProdBase p
left Join Delta D on D.DataHash = P.DataHash
Where D.DataHash is null
--==========================================================
--// Update the Index
--==========================================================
--// Update the delta change
Merge tbStockIndexTrack st
Using (
select tnz_StockNumber,
tnz_name,
DataHash = hashbytes('MD5',concat(tnz_StockNumber,tnz_name))
from TurnersGroup_MSCRM..tnz_stock
) sp on (st.tnz_StockNumber = sp.tnz_StockNumber collate Latin1_General_CI_AS)
When Matched and (st.DataHash = sp.DataHash ) then
Update
Set tnz_name = sp.tnz_name,
modifiedDateOn = getdate()
When not Matched by target Then
Insert (tnz_StockNumber,tnz_name, modifiedDateOn )
Values (tnz_StockNumber,tnz_name, getdate());