 |
|
茫茫網海中的冷日
發生過的事，不可能遺忘，只是想不起來而已！ |
|
|
恭喜您是本站第 1671836
位訪客!
登入 | 註冊
|
|
|
|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2019/7/29 13:27 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]How to get the value of autoincrement of last row at the insert
- How to get the value of autoincrement of last row at the insert
I have googled this problem one week and no thing useful I think am not using the correct word
I am using SQL Server 2008 with t-sql and my need is to optimise my function when I insert a new row.
I have a table with first column is the key of integer autoincrement type and other columns are just for information
When we do an insert, SQL Server increments the key automatically and I have to do a select max to get the value, so is there a way like a global variable like @@IDENTITY or a function to avoid the begin end transaction and select max
Use SCOPE_IDENTITY:
-- do insert
SELECT SCOPE_IDENTITY();
Which will give you:
The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Just ran the code:
INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
and it also works!
What about this for last auto increment value
SELECT IDENT_CURRENT('tableName')-IDENT_INCR('tableName');
If you are using MySQL you get the auto increment ID of the last insert with:
In my case I had to use @@Identity, because I was inserting into a view. It seems that SCOPE_IDENTITY only works for ones you have explicitly created.
See here:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
@@IDENTITY will return the last identity value entered into a table in your current session.
While @@IDENTITY is limited to the current session, it is not limited to the current scope.
If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
原文出處： sql - How to get the value of autoincrement of last row at the insert - Stack Overflow
|
|
|
|
|
討論串
|
[轉貼]How to get the value of autoincrement of last row at the insert (冷日