<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
Insert Error: Column name or number of...
17 posts, Page 1 of 2
1
2
»»
Insert Error: Column name or number of supplied values does not match table definition.
Rate Topic
Display Mode
Topic Options
Author
Message
KKV143
KKV143
Posted Thursday, February 25, 2010 3:14 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 07, 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
Toby White
Toby White
Posted Friday, February 26, 2010 11:38 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 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
Sean Lange
Sean Lange
Posted Friday, February 26, 2010 12:28 PM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 1:17 PM
Points: 8,641,
Visits: 8,273
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
Post #873655
KKV143
KKV143
Posted Sunday, February 28, 2010 4:12 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 07, 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
KKV143
KKV143
Posted Sunday, February 28, 2010 4:18 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 07, 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
Toby White
Toby White
Posted Monday, March 01, 2010 7:35 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 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
KKV143
KKV143
Posted Monday, March 01, 2010 1:44 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 07, 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
Toby White
Toby White
Posted Monday, March 01, 2010 4:59 PM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 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
Toby White
Toby White
Posted Monday, March 01, 2010 5:53 PM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 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
KKV143
KKV143
Posted Monday, March 01, 2010 10:04 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, June 07, 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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
