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

Question

Post reply

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

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

Could this be a case of deferred resolution? Perhaps the tables were altered after the SPs were compiled. Try this:

CREATE PROCEDURE MyProc

AS BEGIN

INSERT INTO #Table1

SELECT

T2.Column1

,T2.Column2

,T2.Column3

FROM #Table2 T2

ORDER BY column1

END

GO

CREATE TABLE

#Table1

(

Column1 int,

Column2 varchar(255),

Column3 int NULL,

Column4 int NULL

)

GO

SELECT *

INTO #Table2

FROM #Table1

INSERT #table2

SELECT 1,'two',3,4

--EXEC MyProc

DROP TABLE #Table2

DROP TABLE #Table1

DROP PROCEDURE MyProc

Uncommenting the EXEC causes an error in SQL 2008 but the above code runs without error.

Doesn't help you solve this particular case though.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

imaceo58 8163 (12/24/2012)
I am getting this same error message ...Column name or number of supplied values does not match table definition.
My table definition is as follows ...
create table GwenF_department(
departmentnamevarchar(25) Primary key,
depttype varchar(10),
DIrectorname varchar(35),
Hiredate date,
Salary int,
RaiseFactor int,
SalaryAdjustment as ( salary * RaiseFactor) ,
descriptionvarchar(50));
and i"m inserting 8 similar rows to this one ...
insert into GwenF_department values('Marketing','MIS','Billy Williams','1972/Apr/01','98000','10',Null,'To pack the seats at Wrigley Field');

It looks Lynn got a solution to your current issue. However I would recommend looking at your data structures. You have a department table with a person's name and salary info in it. You should probably do some reading on database normalization.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

amarlahon Valued Member Points: 58 More actions · Answer 3

Hi All, I was facing same issue with a table. Error was same throughout dev, test and prod. Issue was a Insert trigger with that table. Please check if any trigger is associated with that table. If yes, disable it and check, it should work. Now you can rectify the code in the trigger.