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.
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_220.127.116.1170510_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.
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