Column name or number of supplied values does not match table definition from SQL server

Question

I get this error when I try to generate data for my database:

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

This is the structure of my database:

Create database Newsagents;
USE Newsagents;

CREATE TABLE Client (
ClientID int   NOT NULL, 
Name char(30) NOT NULL,
City char(20) DEFAULT NULL,
Type VARCHAR(15) NOT NULL CHECK (type IN('Individual', 'Company'))
PRIMARY KEY (ClientID)
) ;

CREATE TABLE Product (
ProductNumber char(10) NOT NULL,
ProductName char(20) NOT NULL,
Price float NOT NULL,
isAvailable tinyint NOT NULL,
PRIMARY KEY (ProductNumber)
) ;

CREATE TABLE Sales (
ID INT NOT NULL ,
ClientID INT REFERENCES Client(ClientID),
ProductNumber CHAR(10)  REFERENCES Product(ProductNumber),
Quantity INT NOT NULL,
Price FLOAT NOT NULL ,
Date TIMESTAMP NOT NULL,
PRIMARY KEY ( ID ) 
);

ALTER TABLE sales ADD CONSTRAINT d CHECK (Date > CURRENT_TIMESTAMP);
ALTER TABLE sales ADD CONSTRAINT i CHECK (Quantity > 0);

I than fill my database with some values for Client and Product and I want to generate Sales (using values from Client and Product). This is how I do it:

DECLARE @counter INT
DECLARE @quantity int
DECLARE @prodNum varchar(20)
SET @counter = 0 
WHILE @counter < 10  
BEGIN
SET @quantity = (select FLOOR(RAND()*100))
SET @prodNum = (select TOP 1 ProductNumber from Product Order by NEWID())
insert into Sales values(
   (select TOP 1 ClientID from Client Order by NEWID()),
   (select @prodNum),
   (select @quantity),
   ((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
  DEFAULT
  )
 SET @counter = @counter + 1 
 END

However I get the Column name or number of supplied values does not match table definition. What am I doing wrong?

score 3 · Accepted Answer · answered Dec 04 '13 at 10:27

You're not inserting anything into ID column of Sales. You need to specify it in your query:

insert into Sales values(
   SomeIDHere,
   (select TOP 1 ClientID from Client Order by NEWID()),
   (select @prodNum),
   (select @quantity),
   ((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
  DEFAULT
  )

But maybe you want to have an autoincrement column for your ID?

CREATE TABLE Sales (
ID INT IDENTITY(1,1) NOT NULL  ,
ClientID INT REFERENCES Client(ClientID),
ProductNumber CHAR(10)  REFERENCES Product(ProductNumber),
Quantity INT NOT NULL,
Price FLOAT NOT NULL ,
Date TIMESTAMP NOT NULL,
PRIMARY KEY ( ID ) 
);

In this case, you will need to specify the columns when inserting into Sales

insert into Sales (ClientID, ProductNumber, Quantity, Price, [Date])
values(
   (select TOP 1 ClientID from Client Order by NEWID()),
   (select @prodNum),
   (select @quantity),
   ((select @quantity)*(select TOP 1 Price from Product where ProductNumber = @prodNum)),
  DEFAULT
  )

Thank you, this really helped, however it only works when I don't have this constraint : ALTER TABLE sales ADD CONSTRAINT d CHECK (Date > CURRENT_TIMESTAMP); When I do it gives me the following error: The INSERT statement conflicted with the CHECK constraint "d". The conflict occurred in database "Newsagents", table "dbo.Sales", column 'Date'. Any idea how to fix it? — Bes Sa, Dec 04 '13 at 11:03
This constraint means that the date has to be later than the current date and time. Are you sure you want it? This probably fails because of the tiny difference in getting the date and time and checking the constraint. — Szymon, Dec 04 '13 at 11:22

Column name or number of supplied values does not match table definition from SQL server

1 Answers1

Linked