Table variable, IDENTITY_INSERT, order by problem?

3107869

declare @tt table (sid tinyint identity (1,1), v decimal (18,2), st tinyint)

insert into @tt
select # 1.vs,
case
when ... then 0
when ... then 1
when ... then 2
end si
from fd () # 1
inner join
fs ('') # 3
on # 1.id = # 3.id
order by vs, si

It will go wrong:
Server: Msg 8101, Level 16, State 1, Line 3
You can specify an explicit value for the identity column in the table '@tt' only if a list of columns is used and IDENTITY_INSERT is ON.

If you remove order by vs, si, nothing will go wrong!

vs is decimal data.

10118419

Does the auto-increment column insert values? You can remove the auto-increment

恶魔右指

With table variables, looks like the problem of # 1 temporary table. Change the other table names without using ##
If the insert table has
set IDENTITY_INSERT on-need to be defined to insert
set IDENTITY_INSERT off

3107869

Must be automatically incremented.
Why removing order by is not wrong, but it is not the result I want.

3107869

I used the above code in a custom function.

恶魔右指

insert into @tt
select # 1.vs,
case
when ... then 0
when ... then 1
when ... then 2
end si
from fd () # 1
inner join
fs ('') # 3
on # 1.id = # 3.id
order by vs,
case when ... then 0 when ... then 1 when ... then 2 end-so that nothing goes wrong

3107869

why? Still wrong
Server: Msg 8101, Level 16, State 1, Line 3
You can specify an explicit value for the identity column in the table '@tt' only if a list of columns is used and IDENTITY_INSERT is ON.

恶魔右指

set IDENTITY_INSERT on-need to be defined to insert
--Insert data
set IDENTITY_INSERT off

恶魔右指

set IDENTITY_INSERT off--1
Inserted statement
set IDENTITY_INSERT on-turn on unique ID

恶魔右指

The syntax is:
SET IDENTITY_INSERT table name off


SET IDENTITY_INSERT table name ON