Prev: Returning status/error codes as well as resultsets from stored procedure.
Next: Database, SQL Server, .NET
From: Mark Fitzgerald on 26 Mar 2010 04:52
I had this a while ago. The Merge runs as a single transaction so cannot be
split ie some rows complete successfully while others not. I have to add
further checking into the merge statement. Below is a sql script that
describes our requirement. Duplicate keys were not a problem as we wanted
to update on duplicate, the problem came due to a balance figure not being
allowed to be negative. This was implemented as a check constraint.

Fitz


create database MergeOutput;
go

use MergeOutput;
go

create table Original(id int primary key,name varchar(30),value money)
go

insert into Original values (1,'M',100)
insert into Original values (2,'N',100)
insert into Original values (3,'O',100)
go
---------Add a constraint that will cause a problem later
alter table Original
add constraint NoNegativevalues
check(value > 0)

go

select * from Original
go

create table UpdateValues(id int,name varchar(30),value money)
go

insert into UpdateValues values (3,'O',-200)
insert into UpdateValues values (4,'P',100)
insert into UpdateValues values (5,'Q',-100)
go

/*
Try 1 : As merge statement is a transaction will complete
as a whole or rollback entirely
*/
declare @SuccessfullyChanged table (id int)
merge into Original O
using (select ID,Name,sum(value) as UpdateTotal
from UpdateValues
group by ID,Name) as U
on O.ID = U.ID
when matched then update set value = value + U.UpdateTotal
when not matched then insert values (U.ID,U.Name,U.UpdateTotal)
output inserted.ID into @SuccessfullyChanged;

select * from @SuccessfullyChanged
select * from Original
select * from UpdateValues
go

/*
Try 2 : Test the matched with AND clause to check no problems. This would
need to be
repeated on the not matched if necessary
*/

-- Using OUTPUT and Pre-calculated totals (if required)
declare @SuccessfullyChanged table (ID int)
declare @ComputedValue table (ID int,Name varchar(30),UpdateTotal money)

insert into @ComputedValue
select ID,Name,sum(value)
from UpdateValues
group by ID,Name

merge into Original O
using (select * from @ComputedValue) as U
on O.ID = U.ID
when matched and O.Value + U.UpdateTotal > 0 then update set value = value +
U.UpdateTotal
when not matched and U.UpdateTotal > 0 then insert values
(U.ID,U.Name,U.UpdateTotal)
output inserted.ID into @SuccessfullyChanged;

-- Result : successfully inserted or updated rows
select * from @SuccessfullyChanged

-- Result : unsuccessfully updated rows due to conflict
select ID from UpdateValues
EXCEPT
select ID from @SuccessfullyChanged

-- Result : Updated tables
select * from Original
select * from UpdateValues
go



"Andy B." <a_borka(a)sbcglobal.net> wrote in message
news:eESymVBzKHA.264(a)TK2MSFTNGP05.phx.gbl...
> What I mean is that if rows 1,3 and 5 failed to insert because of a unique
> key violation (or some other reason), I need to be able to identify those
> rows so they can be written to a log somewhere.
> "Uri Dimant" <urid(a)iscar.co.il> wrote in message
> news:uarf50AzKHA.1064(a)TK2MSFTNGP04.phx.gbl...
>> Andy
>> What do you by as what ones failed to insert.??? Will it generate an
>> error and terminate the whole batch?
>> See the below example from Adam Machanic to use a table variavle along
>> with output clause
>>
>> DECLARE @ChangedOrderDetail SalesOrderDetail_Type
>> DECLARE @SalesOrderID int
>>
>>
>> DECLARE @t TABLE (Changed int, ID int)
>>
>>
>>
>> INSERT INTO @t
>> SELECT Changed, SalesOrderDetailID
>> FROM (MERGE Sales.SalesOrderDetail OrderDetail
>> USING @ChangedOrderDetail Chng ON
>> Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
>>
>> AND Chng.[SalesOrderID] =
>> OrderDetail.[SalesOrderID]
>>
>>
>> WHEN MATCHED THEN
>> UPDATE SET [SalesOrderID] = CASE WHEN Chng.Rowversion =
>> OrderDetail.Rowversion
>> THEN Chng.SalesOrderID
>>
>> ELSE
>> OrderDetail.SalesOrderID
>>
>> END
>>
>> ,[CarrierTrackingNumber] = CASE WHEN Chng.Rowversion =
>> OrderDetail.Rowversion
>>
>> THEN
>> Chng.CarrierTrackingNumber
>>
>> ELSE
>> OrderDetail.CarrierTrackingNumber
>>
>> END
>>
>> OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0
>> END Changed
>>
>> , Chng.SalesOrderDetailID) da
>>
>> WHERE CHanged = 0
>>
>>
>>
>> SELECT *
>>
>> FROM @t
>>
>>
>>
>>
>>
>> "Andy B." <a_borka(a)sbcglobal.net> wrote in message
>> news:uc3$vFAzKHA.1236(a)TK2MSFTNGP06.phx.gbl...
>>>I have to write code that needs to log down what rows were inserted as
>>>well as what ones failed to insert. I use a merge statement to
>>>insert/update/delete multiple rows at a time. How would I figure this
>>>out?
>>>
>>
>>
>
>
First  |  Prev  | 
Pages: 1 2
Prev: Returning status/error codes as well as resultsets from stored procedure.
Next: Database, SQL Server, .NET