SQL Server Error Messages - Msg 213¶

Error Message¶

Server: Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Causes:¶

This error occurs when doing an INSERT where the columns list is not specified and the values being inserted, either through the VALUES clause or through a SELECT subquery, are either more than or less than the columns in the table. Here are examples on when the error can occur:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers]
VALUES (1, 'John', 'Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client]
SELECT [ID], [Name], [Address]
FROM [dbo].[NewClient]

Solution / Work Around:¶

To avoid this problem, make sure that the values specified in the VALUES clause or in the SELECT subquery match the number of columns in the INSERT clause. In addition to this, you must specify the columns in the INSERT INTO clause. Although the column list in the INSERT INTO statement is optional, it is recommended that it is always specified so that even if there are any modifications made on the table, either new columns are added or inserted in the middle of the table or columns are deleted, the INSERT statement will not generate this error. (Of course, a different error message will be generated if a column is deleted from the table that is being referenced by the INSERT statement).

Given the samples above, here's how to avoid the error:

-- Sample #1: Using INSERT INTO ... VALUES
CREATE TABLE [dbo].[Customers] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Customers] ( [ID], [Name] )
VALUES (1, 'John Doe')

-- Sample #2: Using INSERT INTO ... SELECT FROM
CREATE TABLE [dbo].[Client] ( [ID] INT, [Name] VARCHAR(100))

INSERT INTO [dbo].[Client] ( [ID], [Name] )
SELECT [ID], [Name]
FROM [dbo].[NewClient]