Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
KKV143
KKV143
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 24
We are migrating from sqlserver 2000 to sql server 2005 and Now i am stcuk with this strange thing which was supported in SQL 2000 but not in SQL 2005.
Though the fix for this problem is prity simple but we are having thousands of SPs and we cannot check and fix this in each SP individually.

The problem is

CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)

INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3
From Table2

I know in the above example I am not providing the value for column4 but this statement runs fine in SQL 2000 and it inserts Null to the column4 but in SQL 2005 it generates the Insert Error: "Column name or number of supplied values does not match table definition"

I tried the above statement in SQL 2005 with compatibility 70 and 80 also but it didn't work.
Is there any Server setting in SQL 2005 which can allow this type of staements?
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 639
I get the same error in SQL 2000 as I expected. Is it possible that you are assuming incorrectly that the procedure ran successfully in SQL Server 2000? I am willing to bet that it did not.

Regards,

Toby
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16570 Visits: 17016
If you know that you want null in column 4 just change your insert.


INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3,
null
From Table2




_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
KKV143
KKV143
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 24
HI Toby White

Thanks for looking into it..............
You are right the above query is not working even in SQL 2000 but I missed an Order by clause in query to mention
If you add an order by clause in query it will start working in SQL 2000.
It is very annoyin... it looks like one of SQL 2000 bugs or may be there is some server settings which allows this kind of behaviour
I hope its Server settings otherwise I will have to go through each SP to locate and fix this for SQL 2005

CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)
INSERT INTO #Table1
SELECT
Table2.Column1,
Table2.Column2,
Table2.Column3
From Table2
order by column1
KKV143
KKV143
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 24
Hi SeanLange

You are right simply null can be inserted....
But its not a matter of 1 SP we have thousands of SPs to be migrated from SQl 2000 to SQl 2005.
It will be very daunting and time consuming work to go through each SP to check and fix it for SQL2005.
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 639
The ORDER BY makes no difference. I run the following code and get the same error as will be shown. The only way your insert is going to work is if #Table1 has an identity column, but that isn't different whether on 2000 or 2005. Please try running the following code yourself and post the results:

DECLARE @Version Varchar(4000)
SELECT @Version = @@Version
PRINT @Version
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)

SELECT *
INTO #Table2
FROM #Table1

INSERT #table2
SELECT 1,'two',3,4

INSERT INTO #Table1
SELECT
Column1
,Column2
,Column3
FROM #Table2
ORDER BY column1

DROP TABLE #Table2
DROP TABLE #Table1

---------------------------The Messages follow-----------------------

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


(0 row(s) affected)
Msg 213, Level 16, State 4, Line 20
Insert Error: Column name or number of supplied values does not match table definition.
KKV143
KKV143
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 24
Hi Toby

You are right again but i made a little modification in ur query and it started working in SQL 2000, I have added table alias as prefix for columns. Here I am using table1 and table2 as example but the real query is based on more than 1 table using join so table alias has to be used as column prefix.

DECLARE @Version Varchar(4000)
SELECT @Version = @@Version
PRINT @Version
CREATE TABLE
#Table1
(
Column1 int,
Column2 varchar(255),
Column3 int NULL,
Column4 int NULL
)

SELECT *
INTO #Table2
FROM #Table1

INSERT #table2
SELECT 1,'two',3,4

INSERT INTO #Table1
SELECT
T2.Column1
,T2.Column2
,T2.Column3
FROM #Table2 T2
ORDER BY column1

DROP TABLE #Table2
DROP TABLE #Table1

-------------------------------

Microsoft SQL Server 2000 - 8.00.2050 (Intel X86)
Mar 7 2008 21:29:56
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


(0 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 639
This is some seriously odd behavior indeed. I am looking into it further.

Regards,

Toby
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 639
I have been unable to make the insert succeed on SQL2k5 with any combination of changes in database settings. I have also failed to find reference to this in SQL2k anywhere online. I am going to have to agree with the other poster that you need to either specify the column list in the insert or the select statement or both. The best thought I have is to use the SQL Upgrade Adviser which should identify all of the offending procedures you will need to fix. Sorry about the bad news Crying

Toby
KKV143
KKV143
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 24
Anyways, Thanks Toby

It is indeed a bad news but anyhow I have to get it done....... poor me Crying
ErmmSad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search