crankshaft February 2016

SQLServer 2008 UPSERT merge without repeating the values

I have created a pseudo UPSERT statement which works, however due to the fact that the data to insert or update might be quite large, in order to reduce network bandwidth, I would like to only define the data to be updated or inserted once.

For brevity, in my example I have only incuded 2 fields and both have very short data length, but in the real system there may be dozens of fields, and some of them may be very long.

MERGE PART AS T 
USING (SELECT ID) AS S 
ON(S.ID = T.ID) 
WHEN MATCHED THEN UPDATE SET ID='ABCD-000',DESCRIPTION='NEW DESCRIPTION' 
WHEN NOT MATCHED THEN INSERT (ID,DESCRIPTION) VALUES('ABCD-000','NEW DESCRIPTION');

In this example, the ID is unique, if therecord exists then it should be updated, if it does not exist then a new record should be inserted.

Answers


i-one February 2016

You can use something like this:

MERGE PART AS T 
USING (
    SELECT
        'ABCD-000' as ID,
        'NEW DESCRIPTION' NewDescription
    ) AS S
ON (S.ID = T.ID)
WHEN MATCHED THEN UPDATE SET T.DESCRIPTION=S.NewDescription
WHEN NOT MATCHED THEN INSERT (ID,DESCRIPTION) VALUES(S.ID, S.NewDescription);

Also, no need to do SET ID='ABCD-000' on WHEN MATCHED THEN UPDATE, because of your mathcing condition is S.ID = T.ID already.

Post Status

Asked in February 2016
Viewed 1,723 times
Voted 11
Answered 1 times

Search




Leave an answer