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

question info

detailed question

the best way can explain question explain desired outcome. i'm trying take set of offices, insert data dbo.deliverylocation table, take output inserted.deliverylocationid , update corresponding office's deliverylocationid field id.

desired outcome example

office data before

officeid | deliverylocationid ----------------------------- 1        | null  2        | null  3        | null 

run sql statement

office data after

officeid | deliverylocationid ----------------------------- 1        | 5  2        | 6  3        | 7 

• delivery location deliverylocationid of 5 created data of office officeid of 1

• delivery location deliverylocationid of 6 created data of office officeid of 2

• delivery location deliverylocationid of 7 created data of office officeid of 3

the problem

per current sql script below, can see have first part (inserting office data delivery location table) complete. second part (updating office corresponding deliverylocationid of created delivery location) not complete, , unsure how go doing that.

my initial thoughts/ solutions

if there way store correlated officeid , deliverylocationid, perhaps loop through them , update offices in second sql statement rather try create 1 sql statement 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     dbo.deliverylocation (locationname, shortname, validatedaddressid, active, disableoffices) output     inserted.deliverylocationid select      officename, officename, validatedaddressid, 0, 0      dbo.office o     officeid in     (     select distinct          officeid               dbo.[user] u             u.deliverylocationid null     ,         u.officeid not null     ) 

i'm not sure doing in insert statement, if use merge statement using office (or query based on office) source, you'll able refer source.officeid inserted.deliverylocationid in output clause. can skip update , delete usage of merge, , use on not matched clause.

when i'm doing things put output temp table, carry out further updates or inserts need there.

in case you've not used merge statement before (or hasn't used of capabilities), fantastic resource on how use them, , how use them well: http://www.made2mentor.com/2012/07/got-the-urge-to-merge/