{"id":12,"date":"2007-05-28T07:17:29","date_gmt":"2007-05-28T07:17:29","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=12"},"modified":"2023-01-16T06:05:00","modified_gmt":"2023-01-16T06:05:00","slug":"how-to-make-a-sql-delta-table","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/how-to-make-a-sql-delta-table","title":{"rendered":"How to make SQL Delta tables"},"content":{"rendered":"<p><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">As I covered in the <a href=\"https:\/\/www.wapshere.com\/missmiis\/?p=11\">SQL efficiency post<\/a>, I snapshot my SQL views to a table before importing. So while I&#8217;m doing that I may as well compare it to the last snapshot, and get myself a nice, quick to import, delta table.<\/span><\/span><\/p>\n<p><span lang=\"EN-GB\"><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">Let&#8217;s say I have a view called <strong>PeopleData<\/strong>. I also need the following three tables:<\/span><\/span><\/span><\/p>\n<ul style=\"margin-top: 0cm;\" type=\"disc\">\n<li class=\"MsoNormal\" style=\"margin: 0cm 0cm 0pt;\"><span style=\"font-family: Times New Roman;\"><strong><span lang=\"EN-GB\">PeopleData_Snapshot<\/span><\/strong><span lang=\"EN-GB\"> (the latest copy of the view)<\/span><\/span><\/li>\n<li class=\"MsoNormal\" style=\"margin: 0cm 0cm 0pt;\"><span style=\"font-family: Times New Roman;\"><strong><span lang=\"EN-GB\">PeopleData_Archive<\/span><\/strong><span lang=\"EN-GB\"> (the previous snapshot)<\/span><\/span><\/li>\n<li class=\"MsoNormal\" style=\"margin: 0cm 0cm 0pt;\"><span style=\"font-family: Times New Roman;\"><strong><span lang=\"EN-GB\">PeopleData_Delta<\/span><\/strong><span lang=\"EN-GB\"> (the difference between the Snapshot and the Archive)<\/span><\/span><\/li>\n<\/ul>\n<p><span lang=\"EN-GB\"><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">The Snapshot and Archive tables have an identical design; the Delta table needs an extra &#8220;ChangeType&#8221; column, which will contain either &#8220;Add&#8221;, &#8220;Delete&#8221; or &#8220;Modify&#8221;. (There&#8217;s an extra possibility for Multivalue tables, but we&#8217;ll go into that <a href=\"https:\/\/www.wapshere.com\/missmiis\/?p=17\">another time<\/a>.)<\/span><\/span> <\/span><\/p>\n<blockquote><p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\"><span lang=\"EN-GB\">As an aside, there&#8217;s a quick way to replicate SQL tables. One you have one table set the way you want you can right-click it and choose All Tasks -&gt; Generate SQL Script -&gt; Preview. Copy and Paste the displayed script into a Query Analyzer window; change the Table name and run the script. Hey presto, instant table!<\/span><\/span><\/span><\/p><\/blockquote>\n<p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\"><span lang=\"EN-GB\"><span lang=\"EN-GB\">The way I generate a Delta table is to look at the Adds, Deletes and Modifies separately. <\/span> <\/span><\/span><\/span><\/p>\n<h4>Add<\/h4>\n<p><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">We want to find all the records that are in the Snapshot table, but not in the Archive table. We do this using the trick of a <em>left outer join<\/em>. Here the table on the left is listed in full, and if a record can&#8217;t be joined with the table on the right we get a NULL value. The objectid column here contains our unique identifying filed &#8211; perhaps a staff number or a uid.<\/span><\/span><\/p>\n<blockquote><p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\" style=\"color: blue;\">INSERT INTO<\/span><span lang=\"EN-GB\"> People_Delta<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">SELECT<\/span> s.*, <span style=\"color: red;\">&#8216;Add&#8217;<\/span> <span style=\"color: blue;\">AS <\/span>ChangeType<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">FROM<\/span> People_Snapshot s <span style=\"color: fuchsia;\">LEFT<\/span> <span style=\"color: #333333;\">OUTER JOIN<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\">People_Archive a <span style=\"color: blue;\">ON <\/span>s.objectid = a.objectid<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">WHERE <\/span>a.objectid <span style=\"color: blue;\">IS<\/span> <span style=\"color: #333333;\">NULL<\/span><\/span><\/p><\/blockquote>\n<h4>Delete<\/h4>\n<p><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">Finding the Deletes is just a matter of lining the tables up the other way around:<\/span><\/span><\/p>\n<blockquote><p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\" style=\"color: blue;\">INSERT INTO<\/span><span lang=\"EN-GB\"> People_Delta<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">SELECT<\/span> a.*, <span style=\"color: red;\">&#8216;Delete&#8217;<\/span> <span style=\"color: blue;\">AS <\/span>ChangeType<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">FROM<\/span> People_Archive a <span style=\"color: fuchsia;\">LEFT<\/span> <span style=\"color: #333333;\">OUTER JOIN<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\">People_Snapshot s <span style=\"color: blue;\">ON <\/span>a.objectid = s.objectid<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">WHERE <\/span>s.objectid <span style=\"color: blue;\">IS<\/span> <span style=\"color: #333333;\">NULL<\/span><\/span><\/p><\/blockquote>\n<h4>Modify<\/h4>\n<p><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">The Modifies are a bit more involved. We do an <em>inner join<\/em> now, because we are only interested in records that are in both tables. We then compare each record field by field to pick up any changes. Luckily SQL is very good at this sort of thing!<\/span><\/span><\/p>\n<blockquote><p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\" style=\"color: blue;\">INSERT INTO<\/span><span lang=\"EN-GB\"> People_Delta<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">SELECT<\/span> a.*, <span style=\"color: red;\">&#8216;Modify&#8217;<\/span> <span style=\"color: blue;\">AS <\/span>ChangeType<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">FROM<\/span> People_Archive a <span style=\"color: blue;\">INNER<\/span><span style=\"color: #333333;\"> JOIN<\/span><\/span><br \/>\n<span style=\"font-family: Times New Roman;\">People_Snapshot s <span style=\"color: blue;\">ON <\/span>a.objectid = s.objectid<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: blue;\">WHERE<\/span> a.field1 &lt;&gt; s.field1<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: #333333;\">OR<\/span> a.field2 &lt;&gt; s.field2<\/span><br \/>\n<span style=\"font-family: Times New Roman;\"><span style=\"color: #333333;\">OR<\/span> a.field3 &lt;&gt; s.field3<\/span><\/p><\/blockquote>\n<p><span lang=\"EN-GB\"><span style=\"font-family: Times New Roman;\">You should now have a table that is suitable for your SQL MA and can be specified on the <strong>Connect Database<\/strong> MA Properties page. <\/span><\/span><\/p>\n<blockquote><p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\">Other relevant posts:<\/span><\/span><\/p>\n<p><span style=\"font-family: Times New Roman;\"><span lang=\"EN-GB\"><a title=\"Permanent link to Speeding up Import times from SQL MAs\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=11\" rel=\"bookmark\"><span style=\"color: #5c6c7d; font-family: Georgia;\">Speeding up Import times from SQL MAs<\/span><\/a><\/span> <\/span><br \/>\n<span lang=\"EN-GB\"><a title=\"Permanent link to Attribute-Level Imports\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=18\" rel=\"bookmark\"><span style=\"color: #5c6c7d;\">Attribute-Level Imports<\/span><\/a><\/span><br \/>\n<span lang=\"EN-GB\"><a title=\"Permanent link to Delta and Multivalued Combined\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=17\" rel=\"bookmark\"><span style=\"color: #59708c;\">Delta and Multivalued Combined<\/span><\/a><\/span><br \/>\n<span lang=\"EN-GB\"><a title=\"Permanent link to A Cheater\u00e2\u20ac\u2122s Guide to DTS Packages\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=20\" rel=\"bookmark\"><span style=\"color: #59708c;\">A Cheater&#8217;s Guide to DTS Packages<\/span><\/a><\/span><br \/>\n<span lang=\"EN-GB\"><a title=\"Permanent link to Some Thoughts on Clearing Delta Tables\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=21\" rel=\"bookmark\"><span style=\"color: #5c6c7d;\">Some Thoughts on Clearing Delta Tables<\/span><\/a><\/span><br \/>\n<span lang=\"EN-GB\"><a title=\"Permanent link to Combining DTS with MIIS Imports\" href=\"https:\/\/www.wapshere.com\/missmiis\/?p=29\" rel=\"bookmark\"><span style=\"color: #59708c;\">Combining DTS with MIIS Imports<\/span><\/a><\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>As I covered in the SQL efficiency post, I snapshot my SQL views to a table before importing. So while I&#8217;m doing that I may as well compare it to the last snapshot, and get myself a nice, quick to import, delta table. Let&#8217;s say I have a view called PeopleData. I also need the&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[34,28,5],"tags":[],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-ilm2007","category-miis2003","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-c","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"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=12"}],"version-history":[{"count":4,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":3338,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/12\/revisions\/3338"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}