{"id":517,"date":"2008-05-28T07:45:56","date_gmt":"2008-05-28T06:45:56","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=517"},"modified":"2008-05-28T07:45:56","modified_gmt":"2008-05-28T06:45:56","slug":"sql-server-upsert-variaciok-4","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/05\/28\/sql-server-upsert-variaciok-4\/","title":{"rendered":"SQL Server UPSERT vari\u00e1ci\u00f3k 4."},"content":{"rendered":"<p>Most j\u00f6n az a megold\u00e1s, amelyik mind k\u00f6z\u00f6tt a leggyorsabb, de egy\u00fattal a legkev\u00e9sb\u00e9 sz\u00e9p. :)<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ncreate proc dbo.UpsertClient4\r\n  @id int,\r\n  @name nvarchar(100)\r\nas\r\nbegin try\r\n    insert dbo.Client (id, name) values (@id, @name)\r\nend try\r\nbegin catch\r\n    if (error_number() = 2627)\r\n        update dbo.Client set name = @name where id = @id\r\nend catch\r\n<\/pre>\n<p>Nekiszaladunk az insertnek, \u00e1m ha a sor l\u00e9tezik m\u00e1r adott id-vel, akkor visszapattanunk, \u00e9s j\u00f6n az update. Csak a constraint violation (2627) eset\u00e9n j\u00f6n az update, egy\u00e9bk\u00e9nt \u00fajra raise-elni kellene a hib\u00e1t, ez nincs benne a p\u00e9ld\u00e1ban.<\/p>\n<p>Hab\u00e1r norm\u00e1l adatb\u00e1zisokn\u00e1l ez a megold\u00e1s j\u00f3val gyorsabb az el\u0151z\u0151ekn\u00e9l, a hibakezel\u00e9st k\u00f6nny\u0171 elrontani, ez\u00e9rt \u00e9szn\u00e9l kell lenni a haszn\u00e1lata sor\u00e1n. De ha a sebess\u00e9g az \u00far, ezt a megold\u00e1st \u00e9rdemes v\u00e1lasztani.<\/p>\n<p>Eml\u00e9keztet\u0151\u00fcl a megold\u00e1sok fut\u00e1si ideje, a kevesebb a jobb:<\/p>\n<p>procid      dur<br \/>\n&#8212;&#8212;&#8212;&#8211; &#8212;&#8212;&#8212;&#8211;<br \/>\n1           152<br \/>\n2           161<br \/>\n3           164<br \/>\n4           64  <---\n5           186\n[\/source]\n<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Most j\u00f6n az a megold\u00e1s, amelyik mind k\u00f6z\u00f6tt a leggyorsabb, de egy\u00fattal a legkev\u00e9sb\u00e9 sz\u00e9p. :) create proc dbo.UpsertClient4 @id int, @name&#8230;<\/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,21,58],"tags":[],"class_list":["post-517","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/517","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=517"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/517\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}