Database Reference
In-Depth Information
source_addresses.customer_objid
)
WHEN NOT MATCHED THEN
INSERT (address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
customer_objid)
VALUES (source_addresses.address_label,
source_addresses.addressline1,
source_addresses.addressline2,
source_addresses.city,
source_addresses.region,
source_addresses.country,
source_addresses.zipcode,
@customer_objid)
WHEN MATCHED AND source_addresses.is_deleted = 1
THEN DELETE
WHEN MATCHED THEN
UPDATE
SET address_label=source_addresses.address_label,
addressline1=source_addresses.addressline1,
addressline2=source_addresses.addressline2,
city=source_addresses.city,
region=source_addresses.region,
country=source_addresses.country,
zipcode=source_addresses.zipcode;
This stored procedure may look a little complex, but it is really simple.
Using the MERGE statement, which is new in SQL Server 2008, we can
write a single statement for each table. MERGE evaluates the tables based
on the provided values to determine whether the record in question is new
and needs to be inserted, or whether it is an existing record that needs to
be updated. Also notice that one of the parameters being passed is called
CustomerAddress. This is a user-defined table data type being passed to
the procedure as a table-valued parameter. Table-valued parameters, also
new to SQL Server 2008, allow us to pass multiple addresses for a cus-
tomer using a single parameter.
Search WWH ::
Custom Search