SQL Server 2008 újdonságok 8. - MERGE utasítás 2.
A következő példát annak illusztrálására raktam össze (nem, ezt már nem loptam, mint a tegnapit :), hogy hogyan lehet a kliensen történt módosításokat, törléseket és beszúrásokat egy füst alatt betolni a szervernek.
Az elképzelt alkalmazás kb. úgy néz ki, mint amit megszoktunk disconnected datadatable-ök esetén. Mivel a datatable ma már nem menő, lehet üzleti ojjektumra is gondolni, ami az adatbázisból jött adatokat fieldekben vagy tömbökben tárolja.
Az adatok egy részhalmazát lehúzzuk a szerverről közönséges, szűrt select segítségével. A kliens ezeket a sorokat módosíthatja, beszúrhat újakat és törölheti is őket. A törlést esetünkben azzal szimulálom, hogy az egyik oszlop értékét !torlendo!-re állítom (itt persze egy olyan string kell, amit garantáltan nem vihet be Mancika). Ha az összes sort levinnénk a kliensre nem kellene ez utóbbi trükk, hisz egy outer join (meg a merge is) meg tudná mondani, mely sorok hiányoznak a kliens halmazából.
No, a kliens tehát összeállítja a módosított adathalmazt, amiben mindhárom művelet szerepel, majd betolja a szervernek. Ha van egy kis esze, a nem változott sorokat nem küldi be, de ezt is lekezeli a példa.
A beköldött adatokat az új MERGE utasítással aprítjuk be.
Kezdjünk neki! Előállítok egy kis táblácskát, ez szimbolizálja a szerver oldali táblánkat.
create schema MergeDemo go select EmployeeID, Title, VacationHours into MergeDemo.EmpServerSide from HumanResources.Employee
Kliens oldalon valahogyan letároljuk az adatokat és módosítjuk, a módosításokat pl. tábla típusú paraméteren keresztül küldhetjük be egyszerre. Ezt a paramétert jelképezi most a következő tábla:
create table MergeDemo.EmpClientSide ( EmployeeID int not null identity(0, -1), Title nvarchar(50) not null, VacationHours smallint not null )
Látható, hogy az identity 0-tól megy visszafelé, így a kliens oldalon az új sorokhoz generált id-k nem ütköznek majd a szerveroldaliakkal. Klasszikus ado.net hack. Rakjunk bele némi adatot, ez menne le a kliens apphoz:
set identity_insert MergeDemo.EmpClientSide on
insert into
MergeDemo.EmpClientSide
(
EmployeeID,
Title,
VacationHours
)
select
EmployeeID,
Title,
VacationHours
from
HumanResources.Employee
where
EmployeeID < 20
set identity_insert MergeDemo.EmpClientSide off
Ha most megnézzük a táblát, ezt találjuk benne:
EmployeeID Title VacationHours ----------- -------------------------------------------------- ------------- 1 Production Technician - WC60 21 2 Marketing Assistant 42 3 Engineering Manager 2 4 Senior Tool Designer 48 5 Tool Designer 9 6 Marketing Manager 40 7 Production Supervisor - WC60 82 8 Production Technician - WC10 83 9 Design Engineer 5 10 Production Technician - WC10 88 11 Design Engineer 6 12 Vice President of Engineering 1 13 Production Technician - WC10 84 14 Production Supervisor - WC50 79 15 Production Technician - WC10 85 16 Production Supervisor - WC60 80 17 Production Technician - WC10 86 18 Production Supervisor - WC60 81 19 Production Technician - WC10 87
Most a kliens fogja az über gridjét, és belebabrál az adatokba. Mivel mi sql táblával szimuláljuk a kliens oldali konténert, sql műveletekkel dolgozok.
(Érdemes jól megnézni azt az insertet, aztán csapkodni a homlokunkat, oracle-ösök csöndben maradnak :).
--Beszúrás szimuláció
insert into MergeDemo.EmpClientSide (Title, VacationHours)
values
('Hasvakaró', 0),
('Hátvakaró', 0)
--Módosítás szimuláció
update MergeDemo.EmpClientSide
set Title = Title + ' alma'
where EmployeeID > 15
--Törlés szimuláció
update MergeDemo.EmpClientSide
set Title = '!torlendo!'
where EmployeeID > 0 and EmployeeID < 4
A kliens adatai ekkor így néznek ki:
EmployeeID Title VacationHours ----------- -------------------------------------------------- ------------- 1 !torlendo! 21 2 !torlendo! 42 3 !torlendo! 2 4 Senior Tool Designer 48 5 Tool Designer 9 6 Marketing Manager 40 7 Production Supervisor - WC60 82 8 Production Technician - WC10 83 9 Design Engineer 5 10 Production Technician - WC10 88 11 Design Engineer 6 12 Vice President of Engineering 1 13 Production Technician - WC10 84 14 Production Supervisor - WC50 79 15 Production Technician - WC10 85 16 Production Supervisor - WC60 alma 80 17 Production Technician - WC10 alma 86 18 Production Supervisor - WC60 alma 81 19 Production Technician - WC10 alma 87 -1 Hasvakaró 0 -2 Hátvakaró 0
Látható, hogy az első három tételt kell majd kiradírozni a szerveren, a 16-19 közöttiek módosultak és a két új sort hátul látjuk, kliens oldalon generált fake id-kkel.
No, most jön a főszereplő, a merge, ami minden módosítást bevisz egy szuszra:
merge into MergeDemo.EmpServerSide s
using MergeDemo.EmpClientSide c
on s.EmployeeID = c.EmployeeID
when matched and c.Title = '!torlendo!' then
delete
when matched and c.Title != '!torlendo!'
and (c.Title != s.Title or
c.VacationHours != s.VacationHours) then
update set
s.Title = c.Title,
s.VacationHours = c.VacationHours
when target not matched then
insert (Title, VacationHours)
values (c.Title, c.VacationHours);
Aki tud angolul, az majdhogynem szövegként olvashatja az utasítást.
Hogy lássuk mi történik a háttérben, az output utasítással (2005 újdonság volt) generáljuk egy táblát az elvégzett műveletekről:
create table #Results
(
ClientEmployeeID int,
ClientTitle nvarchar(50),
ClientVacationHours smallint,
ServerEmployeeID int,
OriginalServerTitle nvarchar(50),
UpdatedOrInsertedServerTitle nvarchar(50),
ServerVacationHours smallint,
Action nvarchar(10)
)
go
merge into MergeDemo.EmpServerSide s
using MergeDemo.EmpClientSide c
on s.EmployeeID = c.EmployeeID
when matched and c.Title = '!torlendo!' then
delete
when matched and c.Title != '!torlendo!'
and (c.Title != s.Title or
c.VacationHours != s.VacationHours) then
update set
s.Title = c.Title,
s.VacationHours = c.VacationHours
when target not matched then
insert (Title, VacationHours)
values (c.Title, c.VacationHours)
output c.EmployeeID, c.Title, c.VacationHours,
coalesce(inserted.EmployeeID, deleted.EmployeeID),
deleted.Title, inserted.Title, inserted.VacationHours,
$ACTION into #Results
;
A $ACTION a merge ajándéka, segít megmutatni, mit művelt a háttérben.
Lássuk hát (csúnya, széles, elnézést érte, view plain-nel, és megfelelően nagy felbontásnál tűrhető):
(9 row(s) affected) ClientEmployeeID ClientTitle ClientVacationHours ServerEmployeeID OriginalServerTitle UpdatedOrInsertedServerTitle ServerVacationHours Action ---------------- -------------------------------------------------- ------------------- ---------------- -------------------------------------------------- -------------------------------------------------- ------------------- ---------- -1 Hasvakaró 0 291 NULL Hasvakaró 0 INSERT -2 Hátvakaró 0 292 NULL Hátvakaró 0 INSERT 1 !torlendo! 21 1 Production Technician - WC60 NULL NULL DELETE 2 !torlendo! 42 2 Marketing Assistant NULL NULL DELETE 3 !torlendo! 2 3 Engineering Manager NULL NULL DELETE 16 Production Supervisor - WC60 alma 80 16 Production Supervisor - WC60 Production Supervisor - WC60 alma 80 UPDATE 17 Production Technician - WC10 alma 86 17 Production Technician - WC10 Production Technician - WC10 alma 86 UPDATE 18 Production Supervisor - WC60 alma 81 18 Production Supervisor - WC60 Production Supervisor - WC60 alma 81 UPDATE 19 Production Technician - WC10 alma 87 19 Production Technician - WC10 Production Technician - WC10 alma 87 UPDATE
Szépen, a terveknek megfelően futottak le a parancsok, örülünk. :)