How to store sql scope_identity() in variable right after insert query in run query activity

Help Studio
, ,
1

How to store sql scope_identity() in variable right after insert query in run query activity
How to get id after insertion in a variable

Thanks

2

@iamhamzanasir

Welcome to the community

Can you be more elaborate on what is the ask here?

And insert query are run using execute non query generally…and select queries are run using execute query activity

Cheers

3

Hi @iamhamzanasir,

  1. In the “Run Query” activity, create a parameter for the variable that will hold the SCOPE_IDENTITY() value. You can do this by clicking on the “Add Parameter” button in the Properties panel and setting the “Direction” to “Output”.
  2. In the SQL query, add the SCOPE_IDENTITY() function to retrieve the last inserted identity value. For example, your query could look like this:
INSERT INTO MyTable (Column1, Column2) VALUES ('Value1', 'Value2');
SELECT SCOPE_IDENTITY();
  1. After the “Run Query” activity, use an “Assign” activity to assign the output parameter of the “Run Query” activity to your variable. For example, if your output parameter is called “OutputParam”, you can use the following expression in the “To” field of the “Assign” activity:
MyVariable = Convert.ToInt32(OutputParam.Value)

This will assign the SCOPE_IDENTITY() value to the “MyVariable” variable as an integer.