SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements

The topic describes the SA0118 analysis rule.

Message

Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements

Description

The rule matches a pair of INSERT and UPDATE statements targeting same table.

How to fix

Review your code and consider using MERGE statement instead of the old pattern for merging tables.

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 
27FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id -- IGNORE:SA0118
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
See Also

Other Resources

Analysis Rules