INSERT fail after set DEFAULT value column

Question

I add column to table as:

RedMarkItemsId (IDENTITY(1,1) NOT NULL PRIMARY KEY)
TaskId (UNIQUEIDENTIFIER)
CreationDate (DATETIME)
CreatedBy (UNIQUEIDENTIFIER)

I want to create column like:

  ALTER TABLE RedMarkItems ADD Item1 BIT DEFAULT(0)

But when I try to insert some values:

INSERT INTO RedMarkItems 
VALUES (
    '9B093907-2072-4F59-A55C-0003CE89EF9E', 
    GETDATE(), 
    '6074CAEA-7A8E-4699-9451-16C2EAF394EF')

It throws error:

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

Why I'm getting this error if I set DEFAULT(0) to that new column?

Table create script:

CREATE TABLE RedMarkItems 
(
    RedMarkItemsId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TaskId UNIQUEIDENTIFIER,
    CreationDate DATETIME,
    CreatedBy UNIQUEIDENTIFIER
)

In my insert statement I have TaskId, CreationDate and CreatedBy, I don't need to specify RedMarkItemsId because it's an IDENTITY(1,1), so it's autonumeric field @gtosto — Jonathan
– Jonathan, Commented Dec 31, 2018 at 19:17
Think about it a bit more: that identity could instead be anywhere positionally in the list of columns, so SQL Server can't guess that the 3 values you supplied are supposed to go into the 3 columns you named; you have to tell it. This is a good thing: the language is being consistent, rather than being overly charitable by making an assumption if the identity happens to be the 1st column, where that assumption could lead to unexpected results and would only work in 1 situation. — underscore_d
– underscore_d, Commented Dec 31, 2018 at 19:40

GMB · Accepted Answer · 2018-12-31 19:22:20Z

8

This piece of SQL is not valid :

INSERT INTO RedMarkItems 
VALUES (
    '9B093907-2072-4F59-A55C-0003CE89EF9E', 
    GETDATE(), 
    '6074CAEA-7A8E-4699-9451-16C2EAF394EF')

When not providing all columns for insert, you need to explicitly specify the target columns. As you are assigning only 3 columns, you probably want :

INSERT INTO RedMarkItems(
    RedMarkItemsId, 
    TaskId, 
    CreationDate
) VALUES (
    '9B093907-2072-4F59-A55C-0003CE89EF9E', 
    GETDATE(), 
    '6074CAEA-7A8E-4699-9451-16C2EAF394EF'
);

answered Dec 31, 2018 at 19:22

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Petro K Over a year ago

It seems, RedMarkItemsId should be replaced with TaskId. He wants to omit the RedMarkItemsId as it is an Identity column...

Collectives™ on Stack Overflow

INSERT fail after set DEFAULT value column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related