topic Re: Last Inserted ID - MSSQL in Talend Studio

Last Inserted ID - MSSQL

root — Sat, 16 Nov 2024 09:14:34 GMT

I have been using tMSSQLLastInsertID to fetch the last inserted id in my table. So far, this method has worked, as most of the data processing was sequential in nature.

Recently, we had some changes and tried inserting data at a quick pace (simulating parallel runs). In that case, the tMSSQLOUTPUT --> tMSSSQLLastInsertID value which was returned is NOT accurate. I just learned that the tMssqlLastInsertID uses "SELECT @@IDENTITY" which may not be the most reliable identifier to use.

I have been reading about this on StackOverFlow and people are suggesting "SCOPE_IDENTITY()" or "OUTPUT clause of the INSERT statement".

Any one has tried these options and can suggest which would be more ideal to use? I really care about the Identity column being returned correctly.

Please advise.

Re: Last Inserted ID - MSSQL

Anonymous — Mon, 09 Oct 2017 07:48:07 GMT

Hi
SCOPE_IDENTITY and @@ IDENTITY are used to retrieve the last identity value generated in any of the tables in the current session, simply by using the global variable of @@ IDENTITY after executing an insert statement to get the ID of the insert record But there is a problem that @@ IDENTITY is global, so it will be reflected in all the scope, an operation, a trigger, a stored procedure called a scope, if there are multiple scopes, the ID number obtained by IDENTITY is the result of the last scope, in this case, we have to use SCOPE_IDENTITY, SCOPE_IDENTITY only returns the values inserted into the current scope; @@ IDENTITY is not limited to a specific scope.
For testing, you can modify the component to use SCOPE_IDENTITY() and verify the result.

Regards
Shong

Re: Last Inserted ID - MSSQL

root — Thu, 12 Oct 2017 18:15:26 GMT

Thank you. Any idea how to modify the component to use SCOPE_IDENTITY?

Re: Last Inserted ID - MSSQL

Anonymous — Fri, 13 Oct 2017 08:39:18 GMT

Hi
You can follow the below steps to modify the componnet to use SCOPE_IDENTITY:

1. Go to the components' directory
<studio install dir>\plugins\org.talend.designer.components.localprovider_6.4.0.20170510_1410\components.
2. Backup the component tMSSqlLastInsertId folder.
3. Open the tMSSqlLastInsertId folder, edit the tMSSqlLastInsertId_begin.javajet file and modify this line:
java.sql.PreparedStatement pstmt_<%=cid %> = conn_<%=cid %>.prepareStatement("SELECT @@IDENTITY");
4. Save the file and restart studio.

Regards
Shong

Re: Last Inserted ID - MSSQL

root — Mon, 16 Oct 2017 15:28:17 GMT

Thank you. I will try that.

Re: Last Inserted ID - MSSQL

root — Mon, 16 Oct 2017 15:31:42 GMT

Another Follow-Up Q @shong

Once I save a fetch a value using the tlastInsertedId, what should be the best method to save it? Should it be a local variable/ context variable/ GlobalMap?

The reason I am asking is in our web service, we sometimes get several requests simultaneously and I have seen some values being over-written.

Please advise. Thanks