| |

VerySource

 Forgot password?
 Register
Search
Author: 3107869

Table variable, IDENTITY_INSERT, order by problem?

[Copy link]

3

Threads

10

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 Unknown

 Author| Post time: 2020-1-2 23:45:01
| Show all posts
Why not?
Server: Message 170, Level 15, State 1, Line 1
Line 1: Syntax error near '@tt'.
Server: Message 170, Level 15, State 1, Line 18
Line 18: Syntax error near '@tt'.


set IDENTITY_INSERT @tt off
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

set IDENTITY_INSERT @tt on
Reply

Use magic Report

0

Threads

93

Posts

46.00

Credits

Newbie

Rank: 1

Credits
46.00

 China

Post time: 2020-1-3 14:57:01
| Show all posts
Table variables need not be set (used this way)
declare @ta table (id int identity (1,1), name varchar (10))
insert @ta
select 'a' union all select 'b'
select * from @ta
Only used for tables
SET IDENTITY_INSERT table name off

SET IDENTITY_INSERT table name ON
Reply

Use magic Report

3

Threads

10

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

 Author| Post time: 2020-1-3 15:21:01
| Show all posts
Why is it wrong to add order by? Remove it without error?
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list