SQL Server 2005 Issue Column name or number of supplied values does not match table definition.

Question

Hello,

I wish to DELETE the data from a table before performing an INSERT INTO, however I keep recieving an error stating:

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

I've also tried defining the columns the data should be entered into as part of the INSERT INTO statement, but then get issues with column names, even though they are correct. I have a feeling the issues relates to me selecting 2 PostCode entries and converting them into 1, but if someone could shed light on this it would be a big help.

My code can be found below, if you want me to add the code where I was sepcifing column names let me know. So you know the fields selected are all the fields in the Course table other than AutoNum which is a auto number primary key and SSMA_TimeStamp, which is a TimeStamp.

BEGIN

DELETE dbo.Course

INSERT INTO dbo.Course

SELECT 
    RTRIM( CAST (sd.[RefNo] AS nvarchar(50))) AS 'Student Ref No',
    sd.[FirstForeName] AS Forename,
    sd.[Surname],
    sd.[Address1],
    sd.[Address2],
    sd.[Address3],
    sd.[Address4],
    sd.[DateOfBirth] AS DOB,
    sd.[PostCodeOut] + ' ' + sd.[PostCodeIn] AS 'Post Code',
    o.[Name] AS 'Course Name',
    o.[Code] As 'Course Code',
    e.[StartDate] AS 'Start Date',
    e.[ExpectedGLH] AS 'Exp GLH',
    e.[ExpectedEndDate] AS 'Expected End Date',
    e.[ActualEndDate] AS 'Actual End Date',
    e.[Grade] AS 'Grade',
    ou.[Description] AS Outcome,
    cs.[Description] AS 'Completion Status',
    sd.[Tel1] AS 'Tel 1'

  FROM [xxxxxxx].[xxxxxx].[dbo].[StudentDetail] sd
  INNER JOIN [xxxxxxx].[xxxxxx].[dbo].[Enrolment] e
  ON sd.[StudentDetailID] = e.[StudentDetailID]
  Inner JOIN [xxxxxxx].[xxxxxx].[dbo].[Offering] o
  ON o.[OfferingID] = e.[OfferingID]
  INNER JOIN [xxxxxxx].[xxxxxx].[dbo].[CompletionStatus] cs
  ON cs.[CompletionStatusID] = e.[CompletionStatusID]
  INNER JOIN [xxxxxxx].[xxxxxx].[dbo].[Outcome] ou
  ON ou.[OutcomeID] = e.[OutcomeID]
  WHERE sd.[AcademicYearID] = '09/10'
  AND
  o.[Code]  LIKE '%-ee%'
  AND
  o.[Name]  LIKE '%-%dl%'
  ORDER BY
  sd.[RefNo]

Answer 1

I would explicitly list the columns in the course table that you are inserting into - this may solve your problem/help find your issue, but also reduce maintenance problems in the future.

Answer 2

It sounds like your 'Course' table does not match your insert statement, either in the number or names of the columns specified (as per the error message).

Could you add the create table code for the 'Course' table as that will show where the discrepancy lies.

Thanks.

Answer 3

To fix this issue you need explicitly specify list of the table's columns in the INSERT INTO statement.

Answer 4

you should add a list of columns to the INSERT statement, see below, where you explicitly list each column from dbo.Course that you intend to populate in your INSERT:

INSERT INTO dbo.Course
                                        ---<<<<<
    (col1, col2, col3, col4, clo5....)  ---<<<<<Add this here
                                        ---<<<<<
SELECT 
    RTRIM( CAST (sd.[RefNo] AS nvarchar(50))) AS 'Student Ref No',
    sd.[FirstForeName] AS Forename,
    sd.[Surname],
    sd.[Address1],
    sd.[Address2],
    sd.[Address3],
    sd.[Address4],
    sd.[DateOfBirth] AS DOB,
    sd.[PostCodeOut] + ' ' + sd.[PostCodeIn] AS 'Post Code',
    o.[Name] AS 'Course Name',
    o.[Code] As 'Course Code',
    e.[StartDate] AS 'Start Date',
    e.[ExpectedGLH] AS 'Exp GLH',
    e.[ExpectedEndDate] AS 'Expected End Date',
    e.[ActualEndDate] AS 'Actual End Date',
    e.[Grade] AS 'Grade',
    ou.[Description] AS Outcome,
    cs.[Description] AS 'Completion Status',
    sd.[Tel1] AS 'Tel 1'

  FROM ....

then make sure that each column in the SELECT list matches each of these columns and in order. From your error, it sounds like you have too many any or too few returned columns in the SELECT.