Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2008 Management Studio Tips and Tricks – Object Explorer Details – More Property Details
Error Messages
Home > SQL Server Error Messages > Msg 213 - Insert Error: Column name or number of supplied values does not match table definition.
SQL Server Error Messages - Msg 213 - Insert Error: Column name or number of supplied values does not match table definition.

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]
Related Articles :