Geek Answers Handbook

SQL - How to use insert output to update a table

QUESTION INFORMATION

Detailed question

The best way to explain your question is to explain my desired result. I am trying to take a specific set of offices, insert its data into the dbo.DeliveryLocation table, and then insert the insert.DeliveryLocationId output and update the corresponding Office DeliveryLocationId field with this identifier.

An example of the desired result.

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 using DeliveryLocationId of 5 was created using Office data with OfficeId of 1

  • A delivery location using DeliveryLocationId of 6 was created using Office data with OfficeId 2

  • Delivery location using DeliveryLocationId of 7 was created using Office data with OfficeId of 3

Problem

SQL script , ( Office ). ( Office DeliveryLocationId ) , , .

/

OfficeId DeliveryLocationId, , SQL, SQL-, .

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

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
    )
+4
2

, INSERT, MERGE, Office ( Office) , .OfficeId, insert.DeliveryLocationId OUTPUT. MERGE ON NOT MATCHED.

, , , .

MERGE ( , ), , well: http://www.made2mentor.com/2012/07/got-the-urge-to-merge/

+2

, ,

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

, Office . , OfficeID DeliveryLocations, , .

0

More articles:


All Articles