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
)
You could do an update join after you insert into delivery location
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.