[SQL Server] Exemple utilisation MERGE, CHECKSUM
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#myTable]'))
DROP TABLE #myTable
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#myTable_hist]'))
DROP TABLE #myTable_hist
CREATE TABLE #myTable(column1 int, column2 varchar(256));
go
CREATE TABLE #myTable_hist(column1 int, column2 varchar(256), HistCheck INT);
GO
INSERT INTO #myTable VALUES (1, 'test');
INSERT INTO #myTable VALUES (2, 'test');
INSERT #myTable_hist(column1, column2, HistCheck)
Select column1, column2, CHECKSUM(column2) from #myTable
-- select * from #myTable_hist
-- Select column1, column2, CHECKSUM(column2) from #myTable
-- CHECKSUM(column2) -1854252673
-- CHECKSUM(*) -1854252689, -1854252705
-- Add new row
INSERT INTO #myTable VALUES (3, 'test');
-- Select column1, column2, CHECKSUM(column2) from #myTable;
-- CHECKSUM(column2) -1854252673
--select * from #myTable
-- select * from #myTable_hist
-- SELECT *, CHECKSUM(Column2) AS CheckVal
--FROM #myTable
WITH cte
AS
(
SELECT *, CHECKSUM(Column2) AS CheckVal
FROM #myTable
)
MERGE
#myTable_hist AS Target_
USING
cte AS Source_
ON Target_.column1 = Source_.column1
WHEN MATCHED AND(Target_.HistCheck <> Source_.CheckVal) THEN
UPDATE SET
Target_.Column2 = Source_.Column2,
Target_.HistCheck = Source_.CheckVal
WHEN NOT MATCHED THEN
INSERT (column1, column2, HistCheck)
VALUES (Source_.column1, Source_.column2, Source_.CheckVal);
SELECT * FROM #myTable
SELECT * FROM #myTable_hist