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

问题描述

StudentTable

 CREATE TABLE [学生]。[StudentDetails]（
 [StudentId] [int] IDENTITY（1,1）NOT NULL，
 [名称] [varchar]（35）NULL，
 [IDNo] [varchar]（10）NULL，
 [NameofGuardian] [varchar]（50）NULL，
 [AddressofCommunication] [ varchar]（200）NULL，
 [MobileNumber] [varchar]（50）NULL，
 [LandlineNumber] [varchar]（20）NULL，
 [电子邮件] [varchar]（50） NULL，
 [AdmissionYear] [varchar]（20）NULL，
 [SectionID] [int] NULL，
 [ClassID] [int] NULL，
 [IsActive] [bit ] NULL，
 [IsDeleted] [bit] NULL，
 [CreatedBy] [int] NULL，
 [CreationDate] [datetime] NULL，
 [ModifiedBy] [int] NULL ，
 [ModifiedDate] [datetime] NULL 
）

USER DEFINED TABLE TYPE

 CREATE TYPE [Student]。[InsertBulkType] AS TABLE（
 [Name] [varchar]（35）NULL，
 [IDNo] [varchar]（10）NULL， 
 [NameofGuardian] [varchar]（50）NULL，
 [AddressofCommunication] [varchar]（200）NULL，
 [MobileNu mber] [varchar]（50）NULL，
 [LandlineNumber] [varchar]（20）NULL，
 [电子邮件] [varchar]（50）NULL，
 [AdmissionYear] [varchar] （20）NULL，
 [SectionID] [int] NULL，
 [ClassID] [int] NULL 
）

当我为下面的批量插入创建程序时出现错误

这是我的程序

创建程序[学生]。[InsertBulkStudent ] 
 @tblStudent InsertBulkType READONLY 
 
 
 AS 
 BEGIN 
 
 INSERT INTO StudentDetails 
 SELECT Name，IDNo，NameofGuardian，AddressofCommunication ，MobileNumber，LandlineNumber，
电子邮件，AdmissionYear，SectionID，ClassID 
来自@tblStudent 
 
结束

错误是

消息213，级别16，状态1，过程InsertBulkStudent，第9行
列名或提供的值的数量与表不匹配定义。

我尝试过：

消息213，级别16 ，状态1，过程InsertBulkStudent，第9行
列名或提供的值与表定义不匹配。

解决方案

由于数据库表和用户定义表中的实际列数在数量上不同，您需要在INSERT语句中显式指定db表的列，如：

  INSERT   INTO  StudentDetails（名称，IDNo，NameofGuardian，AddressofCommunication，MobileNumber，
 LandlineNumber ，Email，AdmissionYear，SectionID，ClassID）
  SELECT 名称，IDNo，NameofGuardian，AddressofCommunication，MobileNumber，
 LandlineNumber，Email，AdmissionYear，SectionID， ClassID 
  FROM   @ tblStuden  
 

您的表格包含第一列whi ch你没有 - 也不能 - 指定：StudentID列。您无法指定它，因为它是一个IDENTITY列，但是如果您没有列出要插入的列，那么SQL总是从第一个开始并继续到那里。没有跳过列的命令，因此您需要指定希望数据进入的列名。无论如何你应该真的这样做，这是一个好习惯！

 创建  PROCEDURE  [InsertBulkStudent] 
  @ tblStudent  InsertBulkType READONLY 
 
 
  AS  
  BEGIN  
 
  INSERT   INTO  StudentDetails（名称，IDNo，NameofGuardian，AddressofCommunication，MobileNumber，LandlineNumber，
电子邮件，AdmissionYear，SectionID，ClassID） 
  SELECT 名称，IDNo，NameofGuardian，AddressofCommunication，MobileNumber，LandlineNumber，
电子邮件，AdmissionYear，SectionID，ClassID 
  FROM   @ tblStudent  
 
  end  

StudentTable

CREATE TABLE [Student].[StudentDetails](
	[StudentId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](35) NULL,
	[IDNo] [varchar](10) NULL,
	[NameofGuardian] [varchar](50) NULL,
	[AddressofCommunication] [varchar](200) NULL,
	[MobileNumber] [varchar](50) NULL,
	[LandlineNumber] [varchar](20) NULL,
	[Email] [varchar](50) NULL,
	[AdmissionYear] [varchar](20) NULL,
	[SectionID] [int] NULL,
	[ClassID] [int] NULL,
	[IsActive] [bit] NULL,
	[IsDeleted] [bit] NULL,
	[CreatedBy] [int] NULL,
	[CreationDate] [datetime] NULL,
	[ModifiedBy] [int] NULL,
	[ModifiedDate] [datetime] NULL
) 

USER DEFINED TABLE TYPE

CREATE TYPE [Student].[InsertBulkType] AS TABLE(
	[Name] [varchar](35) NULL,
	[IDNo] [varchar](10) NULL,
	[NameofGuardian] [varchar](50) NULL,
	[AddressofCommunication] [varchar](200) NULL,
	[MobileNumber] [varchar](50) NULL,
	[LandlineNumber] [varchar](20) NULL,
	[Email] [varchar](50) NULL,
	[AdmissionYear] [varchar](20) NULL,
	[SectionID] [int] NULL,
	[ClassID] [int] NULL
)

when i create procedure for bulk insert below error came
this is my procedure

Create PROCEDURE [Student].[InsertBulkStudent]
      @tblStudent InsertBulkType READONLY
	 
	
AS
BEGIN

 INSERT INTO StudentDetails
      SELECT Name,IDNo,NameofGuardian,AddressofCommunication,MobileNumber,LandlineNumber,
	  Email,AdmissionYear,SectionID,ClassID
      FROM @tblStudent
  
end

error is

Msg 213, Level 16, State 1, Procedure InsertBulkStudent, Line 9
Column name or number of supplied values does not match table definition.

What I have tried:

Msg 213, Level 16, State 1, Procedure InsertBulkStudent, Line 9
Column name or number of supplied values does not match table definition.

解决方案

As your actuall number of columns in the database table and user defined table are different in numbers, you will need to explicitly specify the columns of the db table in the INSERT Statement like:

INSERT INTO StudentDetails(Name,IDNo,NameofGuardian,AddressofCommunication,MobileNumber,
              LandlineNumber,Email,AdmissionYear,SectionID,ClassID)
     SELECT Name,IDNo,NameofGuardian,AddressofCommunication,MobileNumber,
            LandlineNumber,Email,AdmissionYear,SectionID,ClassID
     FROM @tblStuden

Your table contains a first column which you don't - and can't - specify: the StudentID column. You can't specify it because it's an IDENTITY column, but if you do not list the columns to insert to, then SQL always starts at the first and proceeds to there. There is no command to "skip a column" so you need to specify the column names you want data to go into. You should really do that anyway, it's good practice!

Create PROCEDURE [InsertBulkStudent]
      @tblStudent InsertBulkType READONLY
	 
	
AS
BEGIN

 INSERT INTO StudentDetails (Name,IDNo,NameofGuardian,AddressofCommunication,MobileNumber,LandlineNumber,
	  Email,AdmissionYear,SectionID,ClassID) 
      SELECT Name,IDNo,NameofGuardian,AddressofCommunication,MobileNumber,LandlineNumber,
	  Email,AdmissionYear,SectionID,ClassID
      FROM @tblStudent
  
end

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