Misunderstood INSERT when using CASE on ORDER BY

9 Feb 2006 10:28 PM

Ray Costanzo [MVP]

Hi Group,

In the script below, the commented lines, when uncommented, produce:
"An explicit value for the identity column in table '#dest' can only be
specified when a column list is used and IDENTITY_INSERT is ON."

Can anyone explain to me why it is only when I use a CASE statement for my
orderby that SQL Server 2000 thinks that I'm trying to insert on the
"destID" identity column?

-- BEGIN TEST SCRIPT
CREATE TABLE #source (sourceID int identity primary key, somedata
varchar(50) null)
CREATE TABLE #dest (destID int identity primary key, sourceID int, somedata
varchar(50))

INSERT INTO #source (somedata) values ('data 1')
INSERT INTO #source (somedata) values ('data 2')
INSERT INTO #source (somedata) values ('data 3')
INSERT INTO #source (somedata) values ('data 4')

DECLARE @orderby varchar(8); set @orderby = 'somedata'

/*
-- When uncommented, this produces the error
INSERT INTO #dest
SELECT sourceID, somedata FROM #source ORDER BY CASE @orderby WHEN
'somedata' THEN somedata END
*/

INSERT INTO #dest
SELECT sourceID, somedata FROM #source ORDER BY somedata

DROP TABLE #source
DROP TABLE #dest
-- END TEST SCRIPT

Thank you,

Ray at work

9 Feb 2006 10:37 PM

Aaron Bertrand [SQL Server MVP]

Always specify your column list! This makes both scripts work:

INSERT INTO #dest(sourceID, somedata)

Can't explain the different behavior off the top of my head, though...

Show quoteHide quote

"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:uxJ5zgcLGHA.668@TK2MSFTNGP11.phx.gbl...
> Hi Group,
>
> In the script below, the commented lines, when uncommented, produce:
> "An explicit value for the identity column in table '#dest' can only be
> specified when a column list is used and IDENTITY_INSERT is ON."
>
> Can anyone explain to me why it is only when I use a CASE statement for my
> orderby that SQL Server 2000 thinks that I'm trying to insert on the
> "destID" identity column?
>
>
> -- BEGIN TEST SCRIPT
> CREATE TABLE #source (sourceID int identity primary key, somedata
> varchar(50) null)
> CREATE TABLE #dest (destID int identity primary key, sourceID int,
> somedata varchar(50))
>
> INSERT INTO #source (somedata) values ('data 1')
> INSERT INTO #source (somedata) values ('data 2')
> INSERT INTO #source (somedata) values ('data 3')
> INSERT INTO #source (somedata) values ('data 4')
>
>
> DECLARE @orderby varchar(8); set @orderby = 'somedata'
>
> /*
> -- When uncommented, this produces the error
> INSERT INTO #dest
> SELECT sourceID, somedata FROM #source ORDER BY CASE @orderby WHEN
> 'somedata' THEN somedata END
> */
>
> INSERT INTO #dest
> SELECT sourceID, somedata FROM #source ORDER BY somedata
>
>
>
> DROP TABLE #source
> DROP TABLE #dest
> -- END TEST SCRIPT
>
> Thank you,
>
> Ray at work
>

9 Feb 2006 10:42 PM

Ray Costanzo [MVP]

Works for me. Thanks Aaron.

Ray at work

Show quoteHide quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OiBjDkcLGHA.3272@tk2msftngp13.phx.gbl...
> Always specify your column list! This makes both scripts work:
>
> INSERT INTO #dest(sourceID, somedata)
>
> Can't explain the different behavior off the top of my head, though...
>
>
>
>
> "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
> message news:uxJ5zgcLGHA.668@TK2MSFTNGP11.phx.gbl...
>> Hi Group,
>>
>> In the script below, the commented lines, when uncommented, produce:
>> "An explicit value for the identity column in table '#dest' can only be
>> specified when a column list is used and IDENTITY_INSERT is ON."
>>
>> Can anyone explain to me why it is only when I use a CASE statement for
>> my

9 Feb 2006 11:23 PM

Erland Sommarskog

Ray Costanzo [MVP] (my first name at lane 34 dot commercial) writes:

> Can anyone explain to me why it is only when I use a CASE statement for my
> orderby that SQL Server 2000 thinks that I'm trying to insert on the
> "destID" identity column?

Probably a bug. I don't get the error message when using SQL 2005.

But as Aaron pointed out, using explcit column lists is good coding
practice.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx