t\u00e1bla t\u00edpus\u00fa param\u00e9teren<\/a> kereszt\u00fcl k\u00fcldhetj\u00fck be egyszerre. Ezt a param\u00e9tert jelk\u00e9pezi most a k\u00f6vetkez\u0151 t\u00e1bla:<\/p>\n\r\ncreate table MergeDemo.EmpClientSide\r\n(\r\n\tEmployeeID int not null identity(0, -1),\r\n\tTitle nvarchar(50) not null,\r\n\tVacationHours smallint not null\r\n)\r\n<\/pre>\nL\u00e1that\u00f3, hogy az identity 0-t\u00f3l megy visszafel\u00e9, \u00edgy a kliens oldalon az \u00faj sorokhoz gener\u00e1lt id-k nem \u00fctk\u00f6znek majd a szerveroldaliakkal. Klasszikus ado.net hack. Rakjunk bele n\u00e9mi adatot, ez menne le a kliens apphoz:<\/p>\n
set identity_insert MergeDemo.EmpClientSide on<\/p>\n
insert into
\n MergeDemo.EmpClientSide
\n (
\n EmployeeID,
\n Title,
\n VacationHours
\n )
\nselect
\n EmployeeID,
\n Title,
\n VacationHours
\nfrom
\n HumanResources.Employee
\nwhere
\n EmployeeID < 20\n\nset identity_insert MergeDemo.EmpClientSide off\n[\/source]\n\nHa most megn\u00e9zz\u00fck a t\u00e1bl\u00e1t, ezt tal\u00e1ljuk benne:\n\n[source='c']\nEmployeeID Title VacationHours\n----------- -------------------------------------------------- -------------\n1 Production Technician - WC60 21\n2 Marketing Assistant 42\n3 Engineering Manager 2\n4 Senior Tool Designer 48\n5 Tool Designer 9\n6 Marketing Manager 40\n7 Production Supervisor - WC60 82\n8 Production Technician - WC10 83\n9 Design Engineer 5\n10 Production Technician - WC10 88\n11 Design Engineer 6\n12 Vice President of Engineering 1\n13 Production Technician - WC10 84\n14 Production Supervisor - WC50 79\n15 Production Technician - WC10 85\n16 Production Supervisor - WC60 80\n17 Production Technician - WC10 86\n18 Production Supervisor - WC60 81\n19 Production Technician - WC10 87\n[\/source]\n\nMost a kliens fogja az \u00fcber gridj\u00e9t, \u00e9s belebabr\u00e1l az adatokba. Mivel mi sql t\u00e1bl\u00e1val szimul\u00e1ljuk a kliens oldali kont\u00e9nert, sql m\u0171veletekkel dolgozok. \n(\u00c9rdemes j\u00f3l megn\u00e9zni azt az insertet, azt\u00e1n csapkodni a homlokunkat, oracle-\u00f6s\u00f6k cs\u00f6ndben maradnak :).\n\n[source='sql']\n--Besz\u00far\u00e1s szimul\u00e1ci\u00f3\ninsert into MergeDemo.EmpClientSide (Title, VacationHours)\nvalues \n('Hasvakar\u00f3', 0),\n('H\u00e1tvakar\u00f3', 0)\n\n--M\u00f3dos\u00edt\u00e1s szimul\u00e1ci\u00f3\nupdate MergeDemo.EmpClientSide\nset Title = Title + ' alma' \nwhere EmployeeID > 15<\/p>\n
–T\u00f6rl\u00e9s szimul\u00e1ci\u00f3
\nupdate MergeDemo.EmpClientSide
\nset Title = ‘!torlendo!’
\nwhere EmployeeID > 0 and EmployeeID < 4\n[\/source]\n\nA kliens adatai ekkor \u00edgy n\u00e9znek ki:\n\n[source='c']\nEmployeeID Title VacationHours\n----------- -------------------------------------------------- -------------\n1 !torlendo! 21\n2 !torlendo! 42\n3 !torlendo! 2\n4 Senior Tool Designer 48\n5 Tool Designer 9\n6 Marketing Manager 40\n7 Production Supervisor - WC60 82\n8 Production Technician - WC10 83\n9 Design Engineer 5\n10 Production Technician - WC10 88\n11 Design Engineer 6\n12 Vice President of Engineering 1\n13 Production Technician - WC10 84\n14 Production Supervisor - WC50 79\n15 Production Technician - WC10 85\n16 Production Supervisor - WC60 alma 80\n17 Production Technician - WC10 alma 86\n18 Production Supervisor - WC60 alma 81\n19 Production Technician - WC10 alma 87\n-1 Hasvakar\u00f3 0\n-2 H\u00e1tvakar\u00f3 0\n[\/source]\n\nL\u00e1that\u00f3, hogy az els\u0151 h\u00e1rom t\u00e9telt kell majd kirad\u00edrozni a szerveren, a 16-19 k\u00f6z\u00f6ttiek m\u00f3dosultak \u00e9s a k\u00e9t \u00faj sort h\u00e1tul l\u00e1tjuk, kliens oldalon gener\u00e1lt fake id-kkel.\n\nNo, most j\u00f6n a f\u0151szerepl\u0151, a merge, ami minden m\u00f3dos\u00edt\u00e1st bevisz egy szuszra:\n\n[source='sql']\nmerge into MergeDemo.EmpServerSide s\nusing MergeDemo.EmpClientSide c\non s.EmployeeID = c.EmployeeID\n\nwhen matched and c.Title = '!torlendo!' then \n delete\n\nwhen matched and c.Title != '!torlendo!' \n and (c.Title != s.Title or \n c.VacationHours != s.VacationHours) then \nupdate set \n s.Title = c.Title, \n s.VacationHours = c.VacationHours\n\nwhen target not matched then \n insert (Title, VacationHours) \n values (c.Title, c.VacationHours);\n[\/source]\n\n\nAki tud angolul, az majdhogynem sz\u00f6vegk\u00e9nt olvashatja az utas\u00edt\u00e1st.\n\nHogy l\u00e1ssuk mi t\u00f6rt\u00e9nik a h\u00e1tt\u00e9rben, az output utas\u00edt\u00e1ssal (2005 \u00fajdons\u00e1g volt) gener\u00e1ljuk egy t\u00e1bl\u00e1t az elv\u00e9gzett m\u0171veletekr\u0151l:\n\n[source='sql']\n\ncreate table #Results\n(\n ClientEmployeeID int,\n ClientTitle nvarchar(50),\n ClientVacationHours smallint,\n ServerEmployeeID int,\n OriginalServerTitle nvarchar(50),\n UpdatedOrInsertedServerTitle nvarchar(50),\n ServerVacationHours smallint,\n Action nvarchar(10)\n)\ngo\nmerge into MergeDemo.EmpServerSide s\nusing MergeDemo.EmpClientSide c\non s.EmployeeID = c.EmployeeID\n\nwhen matched and c.Title = '!torlendo!' then \n delete\n\nwhen matched and c.Title != '!torlendo!' \n and (c.Title != s.Title or \n c.VacationHours != s.VacationHours) then \nupdate set \n s.Title = c.Title, \n s.VacationHours = c.VacationHours\n\nwhen target not matched then \n insert (Title, VacationHours) \n values (c.Title, c.VacationHours)\n \noutput c.EmployeeID, c.Title, c.VacationHours, \ncoalesce(inserted.EmployeeID, deleted.EmployeeID), \ndeleted.Title, inserted.Title, inserted.VacationHours, \n$ACTION into #Results\n;\n[\/source]\n\nA $ACTION a merge aj\u00e1nd\u00e9ka, seg\u00edt megmutatni, mit m\u0171velt a h\u00e1tt\u00e9rben. \n\nL\u00e1ssuk h\u00e1t (cs\u00fanya, sz\u00e9les, eln\u00e9z\u00e9st \u00e9rte, view plain-nel, \u00e9s megfelel\u0151en nagy felbont\u00e1sn\u00e1l t\u0171rhet\u0151):\n\n[source='c']\n(9 row(s) affected)\nClientEmployeeID ClientTitle ClientVacationHours ServerEmployeeID OriginalServerTitle UpdatedOrInsertedServerTitle ServerVacationHours Action\n---------------- -------------------------------------------------- ------------------- ---------------- -------------------------------------------------- -------------------------------------------------- ------------------- ----------\n-1 Hasvakar\u00f3 0 291 NULL Hasvakar\u00f3 0 INSERT\n-2 H\u00e1tvakar\u00f3 0 292 NULL H\u00e1tvakar\u00f3 0 INSERT\n1 !torlendo! 21 1 Production Technician - WC60 NULL NULL DELETE\n2 !torlendo! 42 2 Marketing Assistant NULL NULL DELETE\n3 !torlendo! 2 3 Engineering Manager NULL NULL DELETE\n16 Production Supervisor - WC60 alma 80 16 Production Supervisor - WC60 Production Supervisor - WC60 alma 80 UPDATE\n17 Production Technician - WC10 alma 86 17 Production Technician - WC10 Production Technician - WC10 alma 86 UPDATE\n18 Production Supervisor - WC60 alma 81 18 Production Supervisor - WC60 Production Supervisor - WC60 alma 81 UPDATE\n19 Production Technician - WC10 alma 87 19 Production Technician - WC10 Production Technician - WC10 alma 87 UPDATE\n[\/source]\n\nSz\u00e9pen, a terveknek megfel\u0151en futottak le a parancsok, \u00f6r\u00fcl\u00fcnk. :)\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
A k\u00f6vetkez\u0151 p\u00e9ld\u00e1t annak illusztr\u00e1l\u00e1s\u00e1ra raktam \u00f6ssze (nem, ezt m\u00e1r nem loptam, mint a tegnapit :), hogy hogyan lehet a kliensen t\u00f6rt\u00e9nt…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,58],"tags":[],"class_list":["post-374","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=374"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/374\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}