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


  • Hello.. I'm new to SQL and I'm trying to create some tables for a class assignment. I'm having a very hard time with this code:


    /* drop table Employee;  */


    create table Employee (


    Emp_Number Integer   NOT NULL




    Name varchar (max) NULL,


    Emp_Street varchar (max)     NULL,


    Emp_City char (30)   NULL,


    Emp_State char (30)   NULL,


    Phone decimal  NULL,


    Salary money  NULL,


    Gender char  NULL,


    Age int NULL,


    Job_Title varchar (max) NULL,




    insert into Employee values (10,'Glenn Edelman','175 Bishops Lane','La Jola','CA',6195550199,10.75,'M',64,'Cashier');

    insert into Employee values (20,'Eric McMullen','763 Church St','Lemongrove','CA',6195550135,6.75,'M',20,'Bagger');

    insert into Employee values (30,'Raj Slentz','123 Torrey Dr','North Clairmont','CA',6195550123,25,'M',34,'Assistant Manager');

    insert into Employee values (40,'Erin Broun','2045 Parkway -Apt 2B','Encinitas','CA',7605550100,6.75,'F',24,'Bagger');

    insert into Employee values (50,'Donald Carpenter','927 Second St','Encinitas','CA',6195550154,7.50,'M',18,'Stocker');

    insert into Employee values (60,'David Esquive','10983 N. Coast Hwy Apt 902','Encinitas','CA',7605550108,9.25,'M',25,'Asst Butchers and Seafood');

    insert into Employee values (70,'Nancy Sharp','10793 Montecino Rd','Ramona','CA',8585550135,10.50,'F',24,'Cashier');


    SELECT * FROM Employee


    When I run this, it tells me:

    Msg 213, Level 16, State 1, Line 29

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

    It errors out at line 29 (where I start to insert my data).

    I've done some research and here's the closest reasoning to my issue that I've found: "The reason for this error is using table name as prefix of a column name and that table name is not included in the from clause. Anothe reason is you may be assigning an alias to the table name and prefix is still the table name." Basically that I'm trying to insert more variables into the table than fields defined. If you have 4 fields and you try to insert 5, you get the error. I'm declaring 10 fields and trying to insert 10...

    I'm listing the columns that I'm trying to insert, declaring my variables, and inserting the data. Where am I going wrong? Any pointers would be much appreciated. Thanks :)


    domenica 13 giugno 2010 01:47


  • There is a typo in the table creation statement 

    Job_Title varchar (max) NULL,



    As you can see, there is an extra comma at the end. Remove it and the code should work.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    domenica 13 giugno 2010 03:42