SQL Server equivalent for Oracle IDENTITY_VAL_LOCAL() function

Question

I am in the middle of a project where we are moving from Oracle to SQL Server.

I was wondering if, like the title says,there are any SQL Server equivalent for Oracle's built in function IDENTITY_VAL_LOCAL().

The SQL statement that I am looking at looks like this:

SELECT IDENTITY_VAL_LOCAL() FROM TABLE(VALUES 1) AS t

Any help would be appreciated.

Best regards

Sara

Answer 1

You can use Scope_Identity() in SQL Server. Also you can use Output clause.

You can check the live demo here. Here Scope_Identity() will work when your table contains the identity column. Using this you will get the last values inserted in the identity column.

Using Output clause you can get the recently added values of any column as well as the deleted values and can be retrieved/stored in a variable/table also.

Answer 2

If you need to get the last ID by your table name, you can use IDENT_CURRENT function:

SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;