SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements |
![]() |
Using a MERGE statement instead of combining INSERT and UPDATE statement for merging tables can improve efficiency.

Executing both INSERT and UPDATE statements successively on the same table may lead to performance issues. This pattern might indicate unnecessary data processing or can signal that a MERGE statement could be more efficient.
For example:
1-- Example of two separate operations 2INSERT INTO TableName (Column1, Column2) VALUES ('Value1', 'Value2'); 3 4UPDATE TableName SET Column2 = 'UpdatedValue' WHERE Column1 = 'Value1';
In this scenario, inserting data into a table only to immediately update it can cause unnecessary I/O operations. Consolidating these actions can reduce server load and improve efficiency.
-
Increased I/O activity due to separate operations, which can slow down database performance.
-
Potential for locking and blocking, affecting concurrency and scalability.

Optimize data merging operations by replacing successive INSERT and UPDATE statements with a single MERGE statement to improve efficiency.
Follow these steps to address the issue:
-
Analyze your current INSERT and UPDATE operations on the table. If they modify the same data, consider if a MERGE statement is applicable.
-
Replace separate INSERT and UPDATE statements with a MERGE statement using the following structure:
-
Define the source of data and specify the target table in the MERGE statement.
-
Use the ON clause to specify the condition for the matched and unmatched records between the source and target.
-
Specify actions for WHEN MATCHED and WHEN NOT MATCHED scenarios, using the UPDATE and INSERT operations as needed.
For example:
1MERGE INTO TableName AS target 2USING (VALUES ('Value1', 'Value2')) AS source (Column1, Column2) 3ON target.Column1 = source.Column1 4WHEN MATCHED THEN 5 UPDATE SET target.Column2 = 'UpdatedValue' 6WHEN NOT MATCHED THEN 7 INSERT (Column1, Column2) VALUES (source.Column1, source.Column2);

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs

There is no additional info for this rule.

SQL
1-- Test 1: A pair of INSERT and UPDATE statements based on SELECT search predicate. Should generate SA0118. 2INSERT INTO dbo.A_Table (Id,Data) 3SELECT Id,Data FROM B_Table B 4WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 5 6UPDATE A_Table SET Data = B.Data 7FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id 8 9-- Test 1a: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should generate SA0118. 10UPDATE A_Table SET Data = B.Data 11FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id 12 13INSERT INTO dbo.A_Table (Id,Data) 14SELECT Id,Data FROM B_Table B 15WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 16 17-- Test 1b: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should generate SA0118. 18UPDATE A SET Data = B.Data 19FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id 20 21INSERT INTO dbo.A_Table (Id,Data) 22SELECT Id,Data FROM B_Table B 23WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 24 25-- Test 2: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should ignore SA0118. 26UPDATE A_Table SET Data = B.Data -- IGNORE:SA0118 27FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id 28 29INSERT INTO A_Table (Id,Data) 30SELECT Id,Data FROM B_Table B 31WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 32 33-- Test 3: A pair of UPDATE and INSERT statements having different target tables. Should NOT generate SA0118. 34INSERT INTO dbo.A_Table2 (Id,Data) 35SELECT Id,Data FROM B_Table B 36WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 37 38UPDATE A_Table1 SET Data = B.Data 39FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id 40 41-- Test 3: Not paired statements because the SELECT statement between them. Should NOT generate SA0118. 42INSERT INTO A_Table (Id,Data) 43SELECT Id,Data FROM B_Table B 44WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id) 45 46SELECT * FROM A_Table 47 48UPDATE A_Table SET Data = B.Data 49FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id |

Message | Line | Column | |
---|---|---|---|
1 | SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements. | 2 | 0 |
2 | SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements. | 10 | 0 |
3 | SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements. | 18 | 0 |
4 | SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements. | 21 | 0 |
