Who has not faced the need to make an Update or Insert? if the record exists or not in the table, it’s not nice to add in a sentence to take the decision or check with a Select if the record exists or not.
At this situation, we can use the MERGE sentences that Microsoft SQL Server provides. This syntax takes the control of what is needed to do. Let’s see more about this syntax.
We create a table where the CODE is the KEY:
CREATE TABLE TestMerge
(Code NVARCHAR(10),
Description NVARCHAR(50),
);
GO
Now we go to generate the MERGE sentence to Insert or Update values in this table:
MERGE dbo.TestMerge AS T
USING (SELECT 'CODE1', 'Description CODE1') AS S (Code, Description)
ON (T.Code = S.Code)
WHEN MATCHED THEN
UPDATE SET Description = S.Description
WHEN NOT MATCHED THEN
INSERT (Code, Description)
VALUES (S.Code, S.Description);
As we can see, after the MERGE word, we add the table name that we want affect, this table will be named TARGET (T)
MERGE dbo.TestMerge AS T
In the next line, we have the USING word, in this line we declare where we go to take the information that will be sent to the TARGET table, this information will be named as SOURCE(S)
USING (SELECT 'CODE1', 'Description CODE1') AS S (Code, Description)
On the ON line, it’s where we go to filter the information and we take the decision of make the Insert or the Update, at this case we go to filter by the Code field
ON (T.Code = S.Code)
Now in the first case, the record exists in our table
WHEN MATCHED THEN
UPDATE SET Description = S.Description
In this case, when the record exists in the table the UPDATE is executed and the Description field it’s updated when the code match.
Now in the second case, the record doesn’t exist in our table and we INSERT the values
WHEN NOT MATCHED THEN
INSERT (Code, Description) VALUES (S.Code, S.Description);
We go to execute all the code and see what happen.
CASE 1: The table does not have any record:
As we can see, the record was added correctly.
CASE 2: Update the existing record on the table:
Now we change the Description from the “Description CODE1” record to “Description CHANGED”
MERGE dbo.TestMerge AS T
USING (SELECT 'CODE1', 'Description CHANGED') AS S (Code, Description)
ON (T.Code = S.Code)
WHEN MATCHED THEN
UPDATE SET Description = S.Description
WHEN NOT MATCHED THEN
INSERT (Code, Description)
VALUES (S.Code, S.Description);
We execute the code
After the code gets executed, the Description value was changed.
The MERGE sentence from Microsoft provide us a big help, just we need be careful when we make the filter to make the Update or Insert, in general could help us to reduce our code.