[SOLVED] SQL Server equivalent for Oracle IDENTITY_VAL

SQL Server equivalent for Oracle IDENTITY_VAL_LOCAL() function

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

Solution

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.