#0308 – SQL Server – INSERT statement – both named parameters and sequence are important

Working with team(s)/team member(s) new to SQL Server is always fun, and helps me gauge the various aspects of SQL Server that I have been taking for granted, but are not so obvious to all.

One of the basic T-SQL programming rules we have in the team is to never have statements like SELECT * FROM dbo.tableName or INSERT INTO dbo.tableName SELECT fieldList FROM dbo.sourceTable in PoC and production code, which effectively is:

Always use column lists and never rely on the wild-card (*) or implicit column lists.

So, one fine day, one of my team members came up to me with a query. He was facing an issue with one of the INSERT statements. He had ensured that all columns were listed in both the INSERT and SELECT statements and also ensured that the number of columns was also matching. Yet, SQL Server was returning an error upon execution of the INSERT statement.

Allow me to reproduce this situation with an example.

USE tempdb;
GO

--Safety Check
IF OBJECT_ID('dbo.ProductList','U') IS NOT NULL
    DROP TABLE dbo.ProductList;
GO

--Create the table
CREATE TABLE dbo.ProductList (ProductId INT IDENTITY(1,1) NOT NULL,
                              Vendor VARCHAR(50) NOT NULL,
                              ProductName VARCHAR(50) NOT NULL,
                              ProductVersion VARCHAR(20) NOT NULL,
                              IsRetired BIT NOT NULL,
                              RecordDate DATETIME  NOT NULL,
                              CONSTRAINT pk_ProductId 
                                PRIMARY KEY CLUSTERED (ProductId)
                             );
GO

Normally, we would insert data into the table using a simple INSERT statement with a SELECT returning the data to be inserted.

USE tempdb;
GO

--Insert test data
INSERT INTO dbo.ProductList (Vendor, ProductName, ProductVersion, IsRetired, RecordDate)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       '95' AS ProductVersion,
       1 AS IsRetired,
       GETUTCDATE() AS RecordDate;
GO

However, this team member had the sequence of the columns in the INSERT statement and in the SELECT misaligned, as shown below.

USE tempdb;
GO

--Notice the mismatch in the column listing for the INSERT and SELECT statements
INSERT INTO dbo.ProductList (IsRetired, ProductName, ProductVersion, RecordDate, Vendor)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       '8' AS ProductVersion,
       0 AS IsRetired,
       GETUTCDATE() AS RecordDate;
GO

This mismatch between the two column lists resulted into the following error:

Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value ‘Microsoft’ to data type bit.

The error is not actually misleading or incorrect in any way. What SQL Server is actually doing is to match up the ordinal positions of the values in the SELECT statement to the column listing in the INSERT statement. In this case, the first column of the SELECT statement is a VARCHAR column (Vendor) whereas the corresponding column in the INSERT is a BIT column (IsRetired). Because the two cannot be implicitly converted, SQL Server returns an error.

CAUTION!

In the above example, SQL Server returned an error because the implicit conversion failed. However, if the implicit conversion had succeeded, no error would have been returned and we would have ended up with incorrect/bad data in our tables. Here’s an example where the columns on the SELECT statement have been misaligned such that implicit conversion happens:

USE tempdb;
GO
INSERT INTO dbo.ProductList (Vendor, ProductName, ProductVersion, IsRetired, RecordDate)
SELECT 'Microsoft' AS Vendor,
       'Windows' AS ProductName,
       0 AS IsRetired,
       '2012' AS ProductVersion,
       GETUTCDATE() AS RecordDate;
GO

This statement would have generated bad data in our data table – the misaligned columns in the SELECT statement would have gone unnoticed unless someone did a SELECT on the records inserted.