SA0020 : Always use a column list in INSERT statements

Introduction

When using an INSERT statement, not specifying the target column list can lead to various issues.

Description

Failing to specify the columns when performing an INSERT operation can pose significant challenges. This oversight makes SQL scripts error-prone, especially when the underlying table structure is modified.

Example of problematic query:

INSERT INTO Employees VALUES (1, 'John Doe', 'HR');

The above query assumes the table column order remains constant. If a new column, say ‘Email’, is added at the beginning, this query could fail or insert incorrect data.

Potential for data integrity issues or runtime errors if table structure changes (e.g., adding or dropping columns).
Decreased code maintainability, as assumptions about the column ordering increase the risk of errors.

How to fix

Explicitly specify the target column list in the INSERT statement to ensure code resilience and maintainability.

Follow these steps to address the issue:

1.Identify the target table and examine its column schema using SSMS or by querying the INFORMATION_SCHEMA.COLUMNS view.

2.List all columns explicitly in the INSERT statement that will receive data, ensuring they match the order of values provided.

3.Adjust the VALUES list to correspond with the specified column order, respecting data types and constraints.

Example of corrected query:

INSERT INTO Employees (EmployeeID, Name, Department)
VALUES (1, 'John Doe', 'HR');

Scope

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

Parameters

Name	Description	Default Value
IgnoreTempTargetTables	Ignore targets that are temporary tables or table variables.	yes

Remarks

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

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName nvarchar(50),
    @FirstName nvarchar(50),
  @JobTitle nvarchar(50),
  @Department nvarchar(50)
AS

    SET NOCOUNT ON;

  -- Target columns are not provided
  INSERT INTO HumanResources.vEmployeeDepartment
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  INSERT INTO #EmployeeDepartment
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  -- Target columns are explicitly provided
  INSERT INTO HumanResources.vEmployeeDepartment(FirstName, LastName, JobTitle, Department)
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  INSERT INTO HumanResources.vEmployeeDepartment    /*IGNORE:SA0020*/
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

Example Test SQL with Automatic Fix

CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName nvarchar(50),
    @FirstName nvarchar(50),
  @JobTitle nvarchar(50),
  @Department nvarchar(50)
AS

    SET NOCOUNT ON;

  -- Target columns are not provided
  INSERT INTO HumanResources.vEmployeeDepartment (BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, Department, GroupName, StartDate)
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  INSERT INTO #EmployeeDepartment
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  -- Target columns are explicitly provided
  INSERT INTO HumanResources.vEmployeeDepartment(FirstName, LastName, JobTitle, Department)
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

  INSERT INTO HumanResources.vEmployeeDepartment    /*IGNORE:SA0020*/
  VALUES (@FirstName,@LastName,@JobTitle,@Department)

Analysis Results

	Message	Line	Column
1	SA0020 : Always use a column list in INSERT statements.	11	1

Analysis Rules