Insert Records and write back identity value

Good morning,

I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.

So for example I want to send 

BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk

to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.

No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions. 

Thanks


Amir


June 19th, 2015 12:05pm

Hello - Can you use this:

-- Create Sample Table
CREATE TABLE tblIDTest ( empid INT IDENTITY(1,1), empname VARCHAR(50) )

-- Declare variables
DECLARE @idValue INT

-- Perform Test Inserts
INSERT INTO tblIDTest (empname) SELECT 'Test Name1'

-- Store Identity in Variable
SELECT @idValue = @@IDENTITY

-- Do whatever with @idValue
SELECT @idValue as IdVal

Hope this works for you !

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 12:26pm

Manu, thanks for the quick reply, this works for 1 record.. how does it work for bulk? I'm trying to insert 30 records (for example) and update 30 Entry_Key's in one SP or query. 

Edit: I just tested it and it just returns last identity value.. so some kind of loop would be needed to utilize this method.

I currently do this through loop on the form itself.. but I view it as inefficient and we started having weird issue recently where record in holding table is duplicated and things don't get updated correctly when that happens (maybe 1 out of 5000 records).

I don't think I noted anywhere I use SQL 2014.

Thanks



  • Edited by Amko4217 14 hours 25 minutes ago
June 19th, 2015 12:38pm

Hello!

You could create a trigger:

CREATE TRIGGER TG_TEST ON tblEWPBulk
AFTER INSERT  AS
DECLARE @ID INT
SELECT @ID = BatchID FROM INSERTED
...
...

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 1:00pm

Rafael, I gave some thought to the table trigger on tblEWPBulk as well as on the holding table (on insert). Trigger on tblEWPBulk will not work since users enter whole batch before trying to upload.. also once record is uploaded it disappears from their form leaving only records that didn't upload with error codes for easier read. Some users might also go back a row if they noticed they entered something wrong, or even double-entered record.

I was thinking trigger on the holding table on insert might be an option but I'm not sure if it will work. I was thinking when record is inserted into holding table write Entry_Key back to tblEWPBulk. Not sure what are issues with that. 

 Thanks for replying.
June 19th, 2015 1:11pm

I ended up creating trigger on my holding table that is set off on insert. Basically after insert I go ahead and update tblEWPBulk UploadEntryKey. I used field called LinkID on the holding table that stores ID of the record from tblEWPBulk and in my update query in the trigger I have that on clause.

I didn't have any issues in the test environment and it was fast. Does anyone forsee any issues with this trigger setup?

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 3:07pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics