Getting errors inserting into table variable.

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 2005 Forums

Transact-SQL (2005)

Getting errors inserting into table variable.

Author

Topic

owen912
Starting Member

31 Posts

Posted - 2007-05-31 : 09:39:19

I am working on upgrading a database from SQL Server 2000 to SQL Server 2005. The upgrade is also going from 32 bit to 64 bit.

There is a stored procedure getting errors related to an INSERT statement on a table variable with 12 columns.

The insert statement looks like this:

INSERT INTO @table
SELECT ColumnA, ColumnB, ColumnC
FROM dbo.MyTable

The above SQL throws the following error:
Insert Error: Column name or number of supplied values does not match table definition.

This insert statement does not throw an error in SQL Server 2000.

So when I re-write the insert statement like this is works:

INSERT INTO @table(ColumnA, ColumnB, ColumnC)
SELECT ColumnA, ColumnB, ColumnC
FROM dbo.MyTable

What I have not found, and was hoping someone could help me with, is some direction to documentation that verifies the problem is related to the upgrade from 2000 to 2005, or from 32 bit to 64bit, or both.

Thanks.

Mike

owen912
Starting Member

31 Posts

Posted - 2007-05-31 : 10:07:13

This is looking more like an issue with the insert statement itself, it just happens to be inserting on a table variable.

Mike

Kristen
Test

22859 Posts

Posted - 2007-05-31 : 10:27:32

I don't believe that

INSERT INTO @table
SELECT ColumnA, ColumnB, ColumnC
FROM dbo.MyTable

will work on SQL2000 if @table has more than 3 columns (well, actually, it should be fine where @table has more than 3 columns PROVIDING the additional columns are IDENTITY and possibly COMPUTED)

Maybe that has changed in SQL 2005?

Its still bad form to use an INSERT with no column list, because if the columns in the table change the statement will break!

Kristen

owen912
Starting Member

31 Posts

Posted - 2007-05-31 : 10:36:27

I don't see how it works in SQL Server 2000 either.

I agree that it's bad form to use an INSERT with no column list. I fear how many other stored procedures I'll find looking like this.

Mike

Kristen
Test

22859 Posts

Posted - 2007-05-31 : 10:49:12

"I don't see how it works in SQL Server 2000 either."

Hehehe ... well, it would be good to check WHY it has appeared to work in SQL 2000. Is it possible that the code has ONLY THREE parameters in the SQL 200 verison, and for some reason more columns in the SQL 2005 version?

This works OK:


DECLARE	@MyTable TABLE
(
	Col1	int IDENTITY(1,1) NOT NULL,
	Col2	int,
	Col3	varchar(10),
	Col4	int
--	, Col5	int
)

INSERT INTO @MyTable
SELECT	2, '333', 4

SELECT	*
FROM	@MyTable

but if I comment in the "Col5" definition it breaks (under SQL 2000), so I don't reckon that this is "silently working" on SQL 2000!

Kristen

owen912
Starting Member

31 Posts

Posted - 2007-05-31 : 11:13:40

That breaks for you?

Someone else is telling me it doens't fail in 2000 because of a bug, which is fixed in 2005.

Kristen
Test

22859 Posts

Posted - 2007-05-31 : 11:33:04

The Col5 variant fails for me

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Dunno if it was broken and fixed by a service pack?

Kristen

owen912
Starting Member

31 Posts

Posted - 2007-05-31 : 11:43:48

Thanks for the input.

Either way, I'll have to put it right for the upgrade.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources