create table # (autoid int identity(1,1),f2 varchar(20) )insert into # select 'aaaaaaa' union all select 'bbbbbbbbbb' union all select 'ccccc' union all select 'dddddddd' union all select 'eeeeeeeee' union all select 'fffffffffff' union all select 'ggggggggg' union all select 'hhhhhhhhh' union all select 'iiiiiiiii' union all select 'jjjjjj' union all select 'kkkkk' union all select 'lllllll' union all select 'mmmmmmmm' union all select 'nnnnnnnn' union all select 'oooooo' union all select 'ppppp' union all select 'qqqqqqq' union all select 'rrrrrr' --这一句可以显示 select @@identity
参考: Use test Go If object_id('c_test') Is Not Null Drop Proc c_testIf object_id('test') Is Not Null Drop Table test Go Create Table test(id int Identity(1,1) Primary Key, x int) Go Create Proc c_test ( @id int Output, @x int ) As Insert Into Test (x) Values (@x) Set @id=Scope_identity() Go Declare @newid int Exec c_test @newid Output,525 Select @newid As [刚Insert的ID]/* 刚Insert的ID ----------- 1 */
参考: Use test Go If object_id('c_test') Is Not Null Drop Proc c_testIf object_id('test') Is Not Null Drop Table test Go Create Table test(id int Identity(1,1) Primary Key, x int) Go Create Proc c_test ( @id int Output, @x int ) As Insert Into Test (x) Values (@x) Set @id=Scope_identity() Go Declare @newid int Exec c_test @newid Output,525 Select @newid As [刚Insert的ID]/* 刚Insert的ID ----------- 1 */
@@identity
贴出你写的来看看
create table tb(id int identity,......)
@@IDENTITY--是得到当前会话的所有范围的最后插入的IDENTITY值
SCOPE_IDENTITY--是得到当前会话的当前范围的最后插入的IDENTITY值
IDENT_CURRENT--是得到指定表的最后插入的IDENTITY值,与会话、范围无关。
Insert 语句; SELECT SCOPE_IDENTITY() 就可以了得到自增ID
我是想在插入前得到自增编号,同时写入到插入的插入数据的某一列中。
例:
1、2、3、4、5
删除3
则
1、2、4、5
删除3之后调用整理功能改为
1、2、3、4
然后添加时就用到5了。
select 'aaaaaaa' union all
select 'bbbbbbbbbb' union all
select 'ccccc' union all
select 'dddddddd' union all
select 'eeeeeeeee' union all
select 'fffffffffff' union all
select 'ggggggggg' union all
select 'hhhhhhhhh' union all
select 'iiiiiiiii' union all
select 'jjjjjj' union all
select 'kkkkk' union all
select 'lllllll' union all
select 'mmmmmmmm' union all
select 'nnnnnnnn' union all
select 'oooooo' union all
select 'ppppp' union all
select 'qqqqqqq' union all
select 'rrrrrr' --这一句可以显示
select @@identity
Use test
Go
If object_id('c_test') Is Not Null
Drop Proc c_testIf object_id('test') Is Not Null
Drop Table test
Go
Create Table test(id int Identity(1,1) Primary Key, x int)
Go
Create Proc c_test
(
@id int Output,
@x int
)
As
Insert Into Test (x) Values (@x)
Set @id=Scope_identity()
Go
Declare @newid int
Exec c_test @newid Output,525 Select @newid As [刚Insert的ID]/*
刚Insert的ID
-----------
1
*/
Use test
Go
If object_id('c_test') Is Not Null
Drop Proc c_testIf object_id('test') Is Not Null
Drop Table test
Go
Create Table test(id int Identity(1,1) Primary Key, x int)
Go
Create Proc c_test
(
@id int Output,
@x int
)
As
Insert Into Test (x) Values (@x)
Set @id=Scope_identity()
Go
Declare @newid int
Exec c_test @newid Output,525 Select @newid As [刚Insert的ID]/*
刚Insert的ID
-----------
1
*/