Home | Functions | Tips & Tricks | SQL Server 2005 | SQL Server 2008 | SQL Server 2012 | Forums | FAQ | Practice Test |    
Tip of the Day : How to Call GETDATE Function in a User-Defined Function
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.

SQL Server Error Messages - Msg 213

Error Message:

Server: Msg 213, Level 16, State 5, 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/Workaround:

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 Topics: