One sproc to insert multiple linked tables, and conditionly modify a field of the just inserted record with the inserted records ID

I am using the following sql sproc to get the Identity of an INSERT to use in subsequent INSERTs of linked tables (thanks Experts Exchange).

(@field1, @field2, @field3, @field4)

 DECLARE @IdentityValue as TABLE(ContactID int);

INSERT INTO primarytable field1, field2
OUTPUT INSERTED.ContactID INTO @IdentityValue -- To get the ID of the inserted record
VALUES @field1, @field2

INSERT INTO linktable field3, field4
SELECT ContactID, @field3, @field4
FROM @IdentityValue

I do not fully understand how this works, but it INSERTs the right data in my tables.

Now in the same sproc I need to INSERT(UPDATE) the @IdentityValue ContactID INTO linktable field4
non working example to illustrate what I am attempting to do:

IF (@flag != 0)

    UPDATE linktable
    SET field4 = @IdentityValue.ContactID  -- yes the ID field and the field4 will be the same
    WHERE linktable.fieldID = @IdentityValue.ContactID --the record just inserted above


Thanks Much
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Yup, @IdentityValue is a table, thus you need a different UPDATE syntax:

SET     field4 = IV.ContactID
FROM    linktable LT
        INNER JOIN @IdentityValue IV ON LT.fieldID = IV.ContactID;above

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:

Thanks much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.