Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert Error: Column name or number of supplied values does not match table definition. Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 3:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 7, 2010 10:05 PM
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?
Post #873046
Posted Friday, February 26, 2010 11:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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
Post #873626
Posted Friday, February 26, 2010 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 13,418, Visits: 12,283
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)
Post #873655
Posted Sunday, February 28, 2010 4:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 7, 2010 10:05 PM
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
Post #874260
Posted Sunday, February 28, 2010 4:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 7, 2010 10:05 PM
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.
Post #874261
Posted Monday, March 1, 2010 7:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #874411
Posted Monday, March 1, 2010 1:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 7, 2010 10:05 PM
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)
Post #874687
Posted Monday, March 1, 2010 4:59 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
This is some seriously odd behavior indeed. I am looking into it further.

Regards,

Toby
Post #874795
Posted Monday, March 1, 2010 5:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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

Toby
Post #874806
Posted Monday, March 1, 2010 10:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 7, 2010 10:05 PM
Points: 11, Visits: 24
Anyways, Thanks Toby

It is indeed a bad news but anyhow I have to get it done....... poor me
Post #874851
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse