列名或提供的值数与SQL Server中的表定义不匹配 [英] Column name or number of supplied values does not match table definition from SQL server

问题描述

当我尝试为数据库生成数据时出现此错误：

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?

推荐答案

您没有在 ID < 销售的/ code>列。您需要在查询中指定它：

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
  )

---

但是也许您想为ID添加自动递增列？

---

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 ) 
);

在这种情况下，插入 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
  )

这篇关于列名或提供的值数与SQL Server中的表定义不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！