Montag, 20. April 2020

MS SQL Merge upsert

multiple record writing - steps to achieve the fastest update of an table:

- create temp database table. This is a copy of the destination table to fill for finally.
- fille the temp database table with the bulk copy function of System.data.sqlClient.
- execute the MERGE script.

MERGE example:

MERGE [dbo].[THE_TABLE] as t
USING [dbo].[TEMP_THE_TABLE] as s
ON t.MY_ID=s.MY_ID AND t.MY_SEQUENCE=s.MY_SEQUENCE
WHEN MATCHED THEN 
UPDATE SET
        t.MY_VALUE=s.MY_VALUE,
        t.MY_TIMESTAMP=s.MY_TIMESTAMP
WHEN NOT MATCHED THEN 
INSERT (MY_ID, MY_SEQUENCE, MY_VALUE, MY_TIMESTAMP)
VALUES (s.MY_ID, s.MY_SEQUENCE, s.MY_VALUE, s.MY_TIMESTAMP);

(t=target, s=source)

Keine Kommentare:

Kommentar posten