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

Question

1.00/5 (1 vote)

See more:

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.

Posted 15-Dec-18 22:50pm

Member 14024377

Updated 15-Dec-18 23:31pm

Add a Solution

2 solutions

Solution 1

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:

SQL

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

Posted 15-Dec-18 23:21pm

Ehsan Sajjad

Updated 15-Dec-18 23:24pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-12-15T23:32:00

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!

SQL

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