Maintaining a foreign key relationship when pasting into tables with an auto-increment identifier

Question

I have two tables: Defect and DefectData. Each Defect may or may not have one or more DefectData. Thus, DefectData has a DefectId column as a foreign key.

The identifier in both tables is the auto increment identifier.

The problem I am facing is that when I want to insert a new Defect and its DefectData, the defect is inserted first and receives Id, but I do not know that this identifier should provide DefectData. My solution is then to select from the defects corresponding to the inserted data in order to obtain Id.

• Insert defect
• Get defect ID
• Insert DefectData (zero or more) with ID of 2.

Setting IdentityInsert and then pasting with my own identifier will not work, as this is done by the web server, and there may be simultaneous calls (am I here?).

Thanks in advance.

Answer 1

The main template is using SCOPE_IDENTITY () to get the new row id from Defect

BEGIN TRAN INSERT Defect () VALUES (...) INSERT DefectData (DefectID, AdditionalNotes, ...) VALUES (SCOPE_IDENTITY(), @AdditionalNotes, ...) COMMIT TRAN