{"id":456,"date":"2008-02-18T11:09:59","date_gmt":"2008-02-18T10:09:59","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/02\/18\/sql-server-2008-ujdonsagok-22-composable-dml-dml-table-source\/"},"modified":"2008-02-18T11:09:59","modified_gmt":"2008-02-18T10:09:59","slug":"sql-server-2008-ujdonsagok-22-composable-dml-dml-table-source","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/02\/18\/sql-server-2008-ujdonsagok-22-composable-dml-dml-table-source\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 22. &#8211; Composable DML (DML table source)"},"content":{"rendered":"<p>Ez egy j\u00f3 dolog lesz.<br \/>\nAz OUTPUT kulcssz\u00f3 m\u00e1r ismer\u0151s lehet 2005-b\u0151l, egy DML (INSERT, UPDATE, DELETE) m\u0171velet \u00e1ltal \u00e9rintett sorokat lehetett kipakolni t\u00e1bla t\u00edpus\u00fa v\u00e1ltoz\u00f3ba vagy lok\u00e1lis v\u00e1ltoz\u00f3kba.<\/p>\n<p>A 2008-ban ezt tov\u00e1bb b\u0151v\u00edtett\u00e9k, \u00edgy a kimenet bemenetk\u00e9nt szolg\u00e1lhat egy INSERT utas\u00edt\u00e1s r\u00e9sz\u00e9re, azaz, \u00f6ssze lehet cs\u00f6vezni mindenf\u00e9le \u00e1tmeneti t\u00e1bla n\u00e9lk\u00fcl a DML m\u0171veleteket. Innen a kompon\u00e1lhat\u00f3 DML elnevez\u00e9s.<\/p>\n<p>L\u00e1ssunk egy egyszer\u0171 p\u00e9ld\u00e1t:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ncreate table t1(col1 int);\r\ncreate table t2(col1 int);\r\n\r\ninsert into t1 values (1),(2),(3);\r\n\r\ninsert into t2(col1)\r\nselect col1 from \r\n(update t1 \r\nset col1 = col1 + 1 \r\noutput inserted.col1) as d;\r\n\r\nselect * from t2\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\ncol1\r\n-----------\r\n2\r\n3\r\n4\r\n<\/pre>\n<p>Egyszer\u0171en nevet kell adni az output kiment\u00e9nek, \u00e9s m\u00e1ris t\u00e1blak\u00e9nt kezelhetj\u00fck.<\/p>\n<p><a href=\"http:\/\/www.sqlskills.com\/blogs\/bobb\/default.aspx\">Bob<\/a> bar\u00e1tunkt\u00f3l \u00e1temelek egy \u00f6sszetettebb audit\u00e1l\u00f3s p\u00e9ld\u00e1t:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty &gt; 0));\r\nCREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta &lt;&gt; 0));\r\nCREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);\r\nGO\r\n\r\nINSERT Stock VALUES(&#039;MSFT&#039;, 10), (&#039;BOEING&#039;, 5);\r\nINSERT Trades VALUES(&#039;MSFT&#039;, 5), (&#039;BOEING&#039;, -5), (&#039;GE&#039;, 3);\r\nGO\r\n\r\nINSERT INTO AuditChanges\r\nSELECT * FROM\r\n(\r\nMERGE Stock S\r\n  USING Trades T\r\n  ON S.Stock = T.Stock\r\n  WHEN MATCHED AND (Qty + T.Delta = 0) THEN\r\n    DELETE\r\n  WHEN MATCHED THEN\r\n    UPDATE SET Qty += T.Delta\r\n  WHEN NOT MATCHED THEN\r\n    INSERT VALUES(Stock, T.Delta)\r\n  OUTPUT $action, T.Stock, inserted.Qty\r\n) tab (action, stock, qty);\r\nGO\r\n\r\nselect * from AuditChanges\r\n<\/pre>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\r\nAction Stock  Qty\r\n------ ------ -----------\r\nDELETE BOEING NULL\r\nINSERT GE     3\r\nUPDATE MSFT   15\r\n<\/pre>\n<p>Aranyos. De az\u00e9rt tudni kell r\u00f3la, hogy ez csak egy kezdem\u00e9ny m\u00e9g az SQL Serverben, v 0.1, majd a k\u00f6vetkez\u0151 verzi\u00f3kban teljes\u00edtik ki. Nem haszn\u00e1lhat\u00f3 csak INSERT-ben az eredm\u00e9ny, csak t\u00e1bl\u00e1ba tud besz\u00farni, view-ba nem, nem lehet a c\u00e9lt\u00e1bl\u00e1n trigger, nem lehet rajta fk vagy pk, nem dolgozhat replik\u00e1ci\u00f3ban. Kb. arra j\u00f3, amire a m\u00e1sodik p\u00e9lda is mutatja, logolni bizonyos m\u0171veleteket.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ez egy j\u00f3 dolog lesz. Az OUTPUT kulcssz\u00f3 m\u00e1r ismer\u0151s lehet 2005-b\u0151l, egy DML (INSERT, UPDATE, DELETE) m\u0171velet \u00e1ltal \u00e9rintett sorokat lehetett&#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,58],"tags":[],"class_list":["post-456","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/456","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=456"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/456\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=456"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}