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.

Description

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:

SQL
Copy
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.

How to fix

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:

  1. Analyze your current INSERT and UPDATE operations on the table. If they modify the same data, consider if a MERGE statement is applicable.

  2. Replace separate INSERT and UPDATE statements with a MERGE statement using the following structure:

  3. Define the source of data and specify the target table in the MERGE statement.

  4. Use the ON clause to specify the condition for the matched and unmatched records between the source and target.

  5. Specify actions for WHEN MATCHED and WHEN NOT MATCHED scenarios, using the UPDATE and INSERT operations as needed.

For example:

SQL
Copy
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);

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
Analysis Results
  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
See Also

Other Resources

Analysis Rules