-2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
VianneN
  • 143
  • 3
  • 12
  • 1
    Maybe you're looking for the [SCOPE_IDENTITY](https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql) function. – AlwaysLearning Jan 17 '20 at 14:36
  • 2
    IDENTITY_VAL_LOCAL() is not an Oracle RDBMS built-in. Javadb (aka Derby) supports it. – APC Jan 17 '20 at 14:37

2 Answers2

2

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.

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
1

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;
Max Zolotenko
  • 1,082
  • 7
  • 13