[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 

Ajouter un Commentaire


Code de sécurité
Rafraîchir