4

I have two tables: Defect and DefectData. Each Defect may or may not have one or many DefectData. As such DefectData has a DefectId column as a foreign-key.

The Id in both tables is an autoincrementing identity.

The problem I am having is that when I want to insert a new Defect and its DefectData the Defect is inserted first and gets an Id, but I don't know what that Id is to give to DefectData. My solution is to then select from defects matching inserted data to get the Id.

  1. Insert Defect
  2. Get that defect's Id
  3. Insert DefectData(zero or many) with Id from 2.

Setting IdentityInsert on then inserting with my own Id will not work as this is run by a webserver and there might be concurrent calls (Am I right here?).

Diagram

Thanks in advance.

TheGwa
  • 1,919
  • 27
  • 44

1 Answers1

5

The basic pattern is this 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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Often you'll want to store the new identity value in a local variable, `SET @NewId = SCOPE_IDENTITY()`, for further work or processing or to return it to the application for future reference. – Philip Kelley Aug 25 '11 at 14:00
  • Thanks. I will give this a try now. If this structure was chained would I nest the BEGIN TRAN? – TheGwa Aug 25 '11 at 14:13
  • @TheGwa: er.. not sure what you mean. That'd be a new question or see this http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – gbn Aug 25 '11 at 14:16
  • @gbn That works like a charm. + 10 ninja points to you. My previous comment was about having another table linked to DefectData say DefectDataDetail in the same way DefectData is linked to Defect. SCOPE_IDENTITY() refers to the Defect Id level. How would you now get DefectData Id for the next level? – TheGwa Aug 25 '11 at 15:27
  • @TheGwa: you'd have to use the OUTPUT clause to capture all IDs http://msdn.microsoft.com/en-us/library/ms177564.aspx – gbn Aug 25 '11 at 15:28
  • @gbn: You champion. That is exactly the right thing. Thanks a lot. – TheGwa Aug 26 '11 at 06:37