Click or drag to resize

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.

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0118

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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 
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

 MessageLineColumn
1SA0118 : Use MERGE instead of INSERT...UPDATE or UPDATE...INSERT statements.20
2SA0118 : Use MERGE instead of INSERT...UPDATE or UPDATE...INSERT statements.100
3SA0118 : Use MERGE instead of INSERT...UPDATE or UPDATE...INSERT statements.180
See Also

Other Resources