This code should not work! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

This code should not work!

Author

Topic

beaglez
Starting Member

2 Posts

Posted - 2008-11-07 : 13:58:38

Hello,

As I was working on upgrading a SQL 2000 database to 2008, I came across something very strange.

I was executing a piece of code that exists in our production environment against the SQL 2008 box, and received the following error:

Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

When I examined the code in question it seemed obvious that the error would be thrown. I was puzzled that this had made it to production. That’s when I realized: running the exact same code in SQL 2000 did not throw the error.

I have created some sample code to illustrate the scenario:

-----------------------------------------------------------
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1

IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2

CREATE TABLE #Temp1
(
C1 VARCHAR(10)
, C2 VARCHAR(10)
, C3 VARCHAR(10)
)

CREATE TABLE #Temp2
(
SomeColumn VARCHAR(10)
)

INSERT INTO #Temp2 (SomeColumn)
VALUES ('Some Value')

-- Following insert should throw an error
INSERT INTO #Temp1
SELECT TOP 1
'C1'
, 'C2'
FROM
#Temp2
ORDER BY
SomeColumn

SELECT *
FROM #Temp1
-----------------------------------------------------------

As you can see I am inserting 2 columns into the table without naming the columns to be inserted into. But if you run this code in a SQL 2000 environment, it does not throw an error. It seems to be related to the ORDER BY clause, but I cannot see the link. I could not find any online documentation related to this.

I’m interested to know if you have seen anything like this. It'll ruin my weekend if I’m not able to get to the bottom of this by this evening!

Thanks!
ASP

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-07 : 14:15:00

It's nothing to worry about.

It's an undocumented feature in SQL Server 2000.

CODO ERGO SUM

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 14:21:33

Just compare their Execution plans... there's a little dirty secret

beaglez
Starting Member

2 Posts

Posted - 2008-11-07 : 18:14:46

I don't get it hanbingl... what am I missing?

I guess this is probably a bug I've uncovered, although I am surprised I cannot find any documentation of it online. To think that I would have missed this momentous discovery if I had stuck to best practices and named the columns in my insert!

quote:
Originally posted by hanbingl

Just compare their Execution plans... there's a little dirty secret

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources