How to use SCOPE_IDENTITY() with sp_executesql

Question

I am trying to get the SCOPE_IDENTITY(). Thank you in advance.

SELECT @InsertTSQL = 'INSERT INTO Test Select from Test2'

EXECUTE sp_executesql @InsertTSQL

print SCOPE_IDENTITY()

Answer 1

You can use @@IDENTITY instead of SCOPE_IDENTITY() to get the assigned identity value in the outer batch scope. The downside with that method is it returns the last assigned identity value on the connection, which can be different than desired if there are triggers on the table.

Alternatively, capture the SCOPE_IDENTITY() value in the inner dynamic SQL scope and return as an output parameter:

DECLARE @ScopeIdentity numeric(38,0);
SELECT @InsertTSQL = 'INSERT INTO Test (col) Select col from Test2;SET @ScopeIdentity = SCOPE_IDENTITY();';
EXECUTE sp_executesql @InsertTSQL, N'@ScopeIdentity numeric(38,0) OUT', @ScopeIdentity = @ScopeIdentity OUT;
SELECT @ScopeIdentity AS ScopeIdentity;

Answer 2

Statement executed with sp_executesql running outer scope, therefore you don't get a value from SCOPE_IDENTITY.

You can use IDENT_CURRENT (Transact-SQL), but if an other process insert also data into the data shortly after you did, then you get that value = latest id.
Better re-think your approach.

Answer 3

Hi @Ludmil G ,

We recommend that you post CREATE TABLE statements for your tables (test2 and test) together with INSERT statements with sample data.

Is there an identity definition in your table Test2? If no, only NULL will be in the output.

Please refer below example and check whether it is helpful.

drop table if exists test2,test  
  
create table test2  
(id int identity(1,1),col varchar(100))  
  
insert into test2(col) values  
('value1'),('value2')  
  
create table test  
(id int,col varchar(100))  
  
declare @InsertTSQL nvarchar(max)  
  
SELECT @InsertTSQL = 'INSERT INTO Test Select * from Test2'  
  
EXECUTE sp_executesql @InsertTSQL  
  
select SCOPE_IDENTITY()  

Output:

2  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.