{"id":130,"date":"2008-07-14T11:30:32","date_gmt":"2008-07-14T11:30:32","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=130"},"modified":"2010-11-23T09:41:39","modified_gmt":"2010-11-23T09:41:39","slug":"sql-query-generate-delta-table","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/sql-query-generate-delta-table","title":{"rendered":"SQL Query: Generate Delta Table"},"content":{"rendered":"<p>Generates lines for Delta table MyTable_Delta by comnparing MyTable to MyTable_Previous.<\/p>\n<p>MyTable has six columns: a unique id and five data columns (col1 &#8211; col5).<\/p>\n<p>See post <a href=\"https:\/\/www.wapshere.com\/missmiis\/?p=129\">SQL 2005 MA: Export and Delta Import<\/a>.<\/p>\n<pre>\/* Adds *\/\r\nINSERT INTO dbo.MyTable_Delta\r\nSELECT a.*, 'Add' AS ChangeType\r\nFROM dbo.MyTable a LEFT OUTER JOIN\r\ndbo.MyTable_Previous b\u00c2\u00a0 ON a.id = b.id\r\nWHERE b.id IS NULL<\/pre>\n<pre>\/* Deletes *\/\r\nINSERT INTO dbo.MyTable_Delta\r\nSELECT b.*, 'Delete' AS ChangeType\r\nFROM dbo.MyTable a RIGHT OUTER JOIN\r\ndbo.MyTable_Previous b\u00c2\u00a0 ON a.id = b.id\r\nWHERE a.id IS NULL<\/pre>\n<pre>\/* Modifies *\/\r\nINSERT INTO dbo.MyTable_Delta\r\nSELECT a.*, 'Modify' AS ChangeType\r\nFROM dbo.MyTable a INNER JOIN\r\ndbo.MyTable_Previous b ON a.id = b.id\r\nWHERE a.col1 &lt;&gt; b.col1\r\nOR a.col2 &lt;&gt; b.col2\r\nOR a.col3 &lt;&gt; b.col3\r\nOR a.col4 &lt;&gt; b.col4\r\nOR a.col5 &lt;&gt; b.col5<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Generates lines for Delta table MyTable_Delta by comnparing MyTable to MyTable_Previous. MyTable has six columns: a unique id and five data columns (col1 &#8211; col5). See post SQL 2005 MA: Export and Delta Import. \/* Adds *\/ INSERT INTO dbo.MyTable_Delta SELECT a.*, &#8216;Add&#8217; AS ChangeType FROM dbo.MyTable a LEFT OUTER JOIN dbo.MyTable_Previous b\u00c2\u00a0 ON a.id&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-130","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-26","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":2,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/130\/revisions"}],"predecessor-version":[{"id":1123,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/130\/revisions\/1123"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}