SQL获取刚插入的记录的自动增长列ID的值
假设表结构如下:
CREATE TABLE TestTable
(
id int identity,
CreatedDate datetime
)
SQL2005获得新增行的自动增长列的语句如下:
insert into TestTable (CreatedDate) output inserted.id values (getdate())
output 是sql server2005的新特性,可以从数据修改语句中返回输出,可以看作是“返回结果的DML”
2005之后 Insert,Delete,Update语句 均支持Output语句。
在Output语句中可以引用inserted和deleted。使用方法同触发器类似。
该方法最大弊端就是需要指定主键。output Inserted.要返回的值。
带有output的insert语句.
@@identity只能返回当前会话最后生产的标识列. 如果一次性插入多条语句的话. 需要返回这些自动生产的标识列. 那么outpu就派上用场了.
declare @temp table(k int, v nvarchar(200))
insert into t1(datacol)
output inserted.keycol, inserted.datacol
into @temp
select lastname from TSQLFundamentals2008.hr.Employees where country='uk'
select * from @temp
带有output的delete语句和update语句也大同小异.
delete语句能操作deleted临时表. 而update的本质是delete和insert一次能操作inserted和updated两张表.
带有output的merge子句.
在merge子句的output输出中可以调用@action函数. 会返回一个代表相应操作的字符串(insert, update, delete)
SQL2000获得新增行的自动增长列的语句如下:
insert into TestTable (CreatedDate) values (getdate())
select @@IDENTITY
用select @@identity得到上一次插入记录时自动产生的ID
如果你使用存储过程的话,将非常简单,代码如下:SET @NewID=@@IDENTITY
@@identity使用注意事项:
我们要慎用@@IDENTITY,原因是 @@IDENTITY 它总是获取最后一条变更数据的自增字段的值,
而忽略了进行变更操作所在的范围约束。比如,我有表 A 和表 B 两个表,现在我在表 A 上定义了一个Insert触发器,当在表 A 中插入一条数据时,自动在表 B 也插入一条数据。此时,大家注意,有两个原子操作:在A中插入一条数据, 接着在B中随后插入一条数据。
现在我们想下,假设上面表 A 和表 B 都有IDENTITY自增域,那么我们在表 A 插入一条数据后,使用了 SELECT @@IDENTITY 输出时,输出的到底是 A 还是 B 的自增域的值呢? 答案很明显,是谁最后插入就输出谁,那么就是 B 了。于是,我本意是想得到 A 的自增域值,结果得到了 B 的自增域值,一只 BUG 随之诞生,搞不好还会影响到整个系统数据的混乱。
因此,对于这种情况,建议大家慎用 @@IDENTITY,而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值,但是它是仅限在一个操作范围之内,而不@@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。
SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,SCOPE_IDENTITY 只返回插入到当前作用域中的值;@@IDENTITY 不受限于特定的作用域。
在C#中使用ExecuteScalar()方法获得第一行第一列的值(即自增id):
object val = cmd.ExecuteScalar();