SQL - How to use the output from an insert to update a table

Question

QUESTION INFO

Detailed Question

The best way I can explain my question is to explain my desired outcome. I'm trying to take a certain set of offices, insert its data into the dbo.DeliveryLocation table, then take the output inserted.DeliveryLocationId and update the corresponding office's DeliveryLocationId field with that id.

Desired Outcome Example

Office Data Before

OfficeId | DeliveryLocationId
-----------------------------
1        | null

2        | null

3        | null

Run the SQL statement

Office Data After

OfficeId | DeliveryLocationId
-----------------------------
1        | 5

2        | 6

3        | 7

• Delivery Location with the DeliveryLocationId of 5 was created with the data of the Office with OfficeId of 1

• Delivery Location with the DeliveryLocationId of 6 was created with the data of the Office with OfficeId of 2

• Delivery Location with the DeliveryLocationId of 7 was created with the data of the Office with OfficeId of 3

The problem

Per my current SQL script below, you can see that I have the first part (inserting the Office data into the Delivery Location table) complete. The second part (updating the Office with the corresponding DeliveryLocationId of the created Delivery Location) is not complete, and I am unsure how to go about doing that.

My initial thoughts/ solutions

If there would be a way to store the correlated OfficeId and DeliveryLocationId, perhaps we could loop through them and update the offices in a second SQL statement rather than try to create one SQL statement that does everything.

REFERENCES

dbo.DeliveryLocation

    [DeliveryLocationId] [int] IDENTITY(1,1) NOT NULL,
    [LocationName] [nvarchar](max) NULL,
    [ShortName] [nvarchar](max) NULL,
    [ValidatedAddressId] [int] NOT NULL,
    [DropoffInstruction] [nvarchar](max) NULL,
    [PickupInstruction] [nvarchar](max) NULL,
    [TaxRate] [decimal](18, 2) NOT NULL,
    [Active] [bit] NOT NULL,
    [DisableOffices] [bit] NOT NULL

dbo.Office

    [OfficeId] [int] IDENTITY(1,1) NOT NULL,
    [OfficeName] [nvarchar](max) NULL,
    [ValidatedAddressId] [int] NOT NULL,
    [ReferralSource] [nvarchar](max) NOT NULL,
    [NumberOfEmployees] [int] NOT NULL,
    [DeliveryLocationId] [int] NULL

Current SQL

insert into
    dbo.DeliveryLocation
(LocationName, ShortName, ValidatedAddressId, Active, DisableOffices)
output
    inserted.DeliveryLocationId
select 
    OfficeName, OfficeName, ValidatedAddressId, 0, 0 
from
    dbo.Office as o
where
    OfficeId in
    (
    select distinct 
        OfficeId 
    from 
        dbo.[User] as u
    where
        u.DeliveryLocationId is null
    and
        u.OfficeId is not null
    )

Answer 1

You could do an update join after you insert into delivery location

update dbo.Office
set o.DeliveryLocationID = dl.DeliveryocationID
from Office o
JOIN DeliveryLocation dl on dl.LocationName = o.OfficeName

Assuming that the Office Names are unique. If they are not you may want to add OfficeID to the DeliveryLocations table, at least temporarily, and join on that.

Answer 2

I'm not sure about doing this in an INSERT statement, but if you use a MERGE statement using Office (or a query based on Office) as the source, you'll be able to refer to source.OfficeId as well as inserted.DeliveryLocationId in the OUTPUT clause. You can simply skip the update and delete usage of the MERGE, and only use the ON NOT MATCHED clause.

When I'm doing things like this I put the output into a temp table, then carry out any further updates or inserts I need to do from there.

In case you've not used the MERGE statement before (or even for anyone who just hasn't used all of their capabilities), this is a really fantastic resource on how to use them, and how to use them well: http://www.made2mentor.com/2012/07/got-the-urge-to-merge/