Menu
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
839
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
IDENT_CURRENT() will give you the last identity value inserted into a specific table from any scope, by any user. @@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope.
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
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.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
