IDENTITY, insert and order-by
-
This is not strictly a problem, as the workaround and best-practices are quite obvious. But I still wondered whether anybody have an explanation as to what exactly happens.
I have two tables, defined like this:
create table table_a (
ID int,
line_no int identity(1,1),
result varchar(4000))
go
create table table_b(
code_a varchar(12),
code_b varchar(12),
message varchar(100)
)
go
-- Next, a bit of dummy population:
insert table_b values ('A', 'B', 'Row one')
insert table_b values ('B', 'A', 'Row two')
insert table_b values ('A', 'C', 'Row three')
And then, finally to the definition of my question. The first statement below will succeed. The second will fail, and indicate that it is not possible to assign an explicit value to the identity column.
-- Statement 1 (Success)
insert table_a
select 1, convert(char(12), code_a) + ' - ' + convert(char(12), code_b) + ' - ' + message
from table_b
-- Statement 2 (Fails)
insert table_a
select 1, convert(char(12), code_a) + ' - ' + convert(char(12), code_b) + ' - ' + message
from table_b
order by convert(char(12), code_a)
Since the only difference between the two statements is the order-by statement, I guess this has something to do with it. But if you have any explanation to exactly what happens, it would be interesting.
And yes, of course, personally I would never write an insert-statement like that
, I would always include the columns:-- Statement 3 (The proper way?)
insert table_a (ID, result)
select 1, convert(char(12), code_a) + ' - ' + convert(char(12), code_b) + ' - ' + message
from table_b
order by convert(char(12), code_a)
-
Well, although I don't know this for certain, my supposition (and proof by expiramentation) is that the ORDER BY clause creates a temporary column for internal sorting when a sort value is not in the select list. Since it isn't supposed to be visible to the outside world it is perceived as NULL to the INSERT command but is kind-of sort-of there none-the-less with real values for sorting. The identity thing is just a furthor quirk since it is an optional column to the INSERT. The following illustrates my case:
create table table_c (
ID int,
result varchar(4000),
result2 varchar(4000))
go
truncate table table_c
insert table_c
select 1, code_a
from table_b
order by convert(char(12), code_a)
SELECT * FROM table_c
truncate table table_c
insert table_c
select 1, code_a
from table_b
order by code_a
truncate table table_c
insert table_c
select 1, convert(char(12), code_a) AS TheCodeA
from table_b
order by TheCodeA
-
Thanks Aaron,
I think your explanation makes sense, and it does seem to be supported by your examples. As I said, this was not an urgent problem, but I got very curious about the behaviour.
-
Here is your problem:
You have a table with 3 columns. In the insert statement you put values only for 2 columns. In such cases you have to explicitly indicate what columns you want the insert values go to.
create table table_a (
ID int,
line_no int identity(1,1),
result varchar(4000))
INSERT table_a(ID, result)
SELECT 1, convert(char(12), code_a) + ' - ' + convert(char(12), code_b) + ' - ' + message
from table_b
order by convert(char(12), code_a)
Leah Kats
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply