|
|
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]
|