CODEWITHSUNDEEP
sqlsql-servert-sqldefault
Ren
Ren

Reputation: 112

Insert query: Column name or number of supplied values does not match table definition

This is a table that auto increments the ID, takes the time input, and sets the default of the total column as zero and the date column with getdate()

CREATE TABLE OrderPlaced (
    OrderID bigint IDENTITY(1,1) PRIMARY KEY,
    PlacedAt time NOT NULL,
    PlacedOn DATE NOT NULL default getdate(),
    Total MONEY default 0
)

So the only value that I have to insert is the time.

insert into OrderPlaced values ('13:40');

However on insertion SQL Server gives me this error:

Column name or number of supplied values does not match table definition.

Upvotes: 0

Views: 8837

Answers (2)

Thom A
Thom A

Reputation: 96004

The error is telling you the problem. Your table, OrderPlaced, has four columns, but you only supply one column in the INSERT and don't tell the instance what column to insert it into.

For your table, if you don't specify the columns then it will expect values for PlacedAt, PlacedOn and Total; it won't expect a value for OrderID as it is an IDENTITY and you haven't turned on IDENTITY_INSERT. As the instance is expecting 3 columns, and you only provide one, you get an error telling you that the number of columns don't match (3 != 1).

If you only want to insert a value into PlacedAt, and allow the rest of the columns use their DEFAULT values then define in your INSERT clause you only want to provide a value for PlacedAt:

INSERT INTO dbo.OrderPlaced(PlacedAt)
VALUES ('13:40');

Or, if you are (oddly) opposed to defining the columns in the INSERT clause, then tell the instance that the other 2 columns need their DEFAULT values.

INSERT INTO dbo.OrderPlaced
VALUES ('13:40',DEFAULT, DEFAULT);

Though I don't recommend this solution, be explicit as then if you change definition of the table, the above statement will fail.

Upvotes: 2

user14671387
user14671387

Reputation:

You need to specify the column name as well, like this:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

So it will be like this:

insert into OrderPlaced values ('13:40');

Upvotes: 0

Related Questions