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.