Hi, here,
I am trying to come with a block of merge script, and definitely could use some helps.
According to this msdn, part B ( Using MERGE to perform UPDATE and DELETE operations on a table) seems is something I need. But I had hard time to adopt it.
https://msdn.microsoft.com/en-us/library/bb510625.aspx
Here is the sample data.
Couple points to note.
This sample and the logic in real code works. I am looking for a way to come up with a generic Update(or Merge?) that will apply to all cases. So I don't have to custom code against each pair of records based on the actual data.
I have not used the F_ID to link the pair, but given a pair of P_ID, one record is identified as to keep and another is to purge into first one if any column on first one is null or empty and second one has values.
Ideally, the delete will come after successfully executing above merge.
The solution could be in a stored proc, taking a pair of P_IDs, as the MSDN has suggested.
THANKS!
use tempdb
go
if object_ID('tempdb..#testTbl') is not null drop table #testTbl;
create table #testTbl (P_ID int, F_ID int, B_Date smalldatetime, C_Col varchar(20), D_Col varchar(20))
insert into #testTbl (P_ID, F_ID, B_Date, C_Col, D_Col)
Values (123, 222, Null, 'ABC', 'test'),
(321, 222, GetDate()-10, null, null),
(124, 333, GetDate(), null, ''),
(421, 333, null, 'xyz', '2zy')
Select * from #testTbl
update #testTbl
set B_Date = (Select B_Date from #testTbl where P_ID = 321)
from #testTbl where P_ID = 123
update #testTbl
set
C_Col = (Select C_Col from #testTbl where P_ID = 421),
D_Col = (Select D_Col from #testTbl where P_ID = 421)
from #testTbl where P_ID = 124
Delete from #testTbl where P_ID =321
Delete from #testTbl where P_ID =421
drop table #testTbl