CODEWITHSUNDEEP
sqlinsertsql-update
Doctor Lol
Doctor Lol

Reputation: 45

how to INSERT INTO for each updates row

Have a SQL INSERT statement 

INSERT INTO [tbAddress]
           ([CompanyName])
 VALUES
           ('undefined'),

and UPDATE statement

UPDATE tbOrganisation
SET AddressID=INSERT INTO [tbAddress]
           ([CompanyName])
     VALUES
           ('undefined' )

WHERE AddressID = 783240

I need insert to table Address new same record for each record with AddressID = 783240, help please.

Upvotes: 0

Views: 49

Answers (2)

Veselin Aleksandrov
Veselin Aleksandrov

Reputation: 3

I'm not 100% sure that I understood the question, but I think this should help:

UPDATE tbOrganisation SET AddressID='undefined' WHERE AddressID=783240;

Is this what you had in mind?

Upvotes: 0

Adrian Wragg
Adrian Wragg

Reputation: 7401

You need to make use of SCOPE_IDENTITY:

    DECLARE @addressId int

    INSERT INTO [tbAddress] ([CompanyName]) VALUES ('undefined')
    SET @addressId = SCOPE_IDENTITY()

    UPDATE tbOrganisation SET AddressID = @addressId WHERE AddressID = 783240

More information on Technet here: http://technet.microsoft.com/en-us/library/ms190315.aspx. Note that you may come across @@IDENTITY as another solution, however there are subtle differences (I believe, related to triggers).

Upvotes: 1

Related Questions