{"id":543,"date":"2015-11-25T14:34:42","date_gmt":"2015-11-25T09:04:42","guid":{"rendered":"http:\/\/pramodsingla.wordpress.com\/?p=543"},"modified":"2015-11-25T14:34:42","modified_gmt":"2015-11-25T09:04:42","slug":"sql-server-check-data-difference-between-two-tables","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=543","title":{"rendered":"SQL Server-Check Data difference between two tables"},"content":{"rendered":"<p style=\"text-align:justify;\"><strong><span style=\"color:#ff0000;\">Problem:\u00a0<\/span><\/strong>How to compare tables and\/or data to\u00a0identify data changes between them?<\/p>\n<p style=\"text-align:justify;\"><span style=\"color:#008000;\"><strong>Solution:\u00a0<\/strong><\/span>In data migration, we often have to compare two tables and identify a record in one table that have no record in the corresponding table. The steps for comparing tables are as follows:-<\/p>\n<ul style=\"text-align:justify;\">\n<li><span style=\"color:#3366ff;\"><strong>Create two tables with similar structure:<\/strong><\/span><\/li>\n<\/ul>\n<p style=\"padding-left:60px;text-align:justify;\">use tempdb<br \/>\ngo<br \/>\n\/**Create source table*\/<br \/>\nif exists (select 1 from sys.objects where name=&#8217;Source&#8217;)<br \/>\ndrop table Source<br \/>\ncreate table Source (a int,c int)<br \/>\n\/**Create target table*\/<br \/>\nif exists (select 1 from sys.objects where name=&#8217;Target&#8217;)<br \/>\ndrop table Target<br \/>\ncreate table Target (a int,c int)<\/p>\n<ul style=\"text-align:justify;\">\n<li><span style=\"color:#3366ff;\"><strong>Insert some data into both source and target tables:<\/strong><\/span><\/li>\n<\/ul>\n<p style=\"padding-left:60px;text-align:justify;\">\/*Insert dummy records*\/<br \/>\ninsert into Source values(1,1)<br \/>\ninsert into Source values(2,2)<br \/>\ninsert into Source values(3,3)<br \/>\ninsert into Source values(4,4)<\/p>\n<p style=\"padding-left:60px;text-align:justify;\">insert into Target values(1,1)<br \/>\ninsert into Target values(2,2)<br \/>\ninsert into Target values(8,4)<br \/>\ninsert into Target values(5,5)<\/p>\n<ul style=\"text-align:justify;\">\n<li><span style=\"color:#3366ff;\"><strong>Compare data of both the tables:<\/strong><\/span><\/li>\n<\/ul>\n<p style=\"padding-left:60px;text-align:justify;\">\/**Compare data of two tables***\/<br \/>\nSELECT &#8216;Type&#8217;=CASE<br \/>\nWHEN a.c IS NULL THEN &#8216;I&#8217;<br \/>\nWHEN b.c IS NULL THEN &#8216;D&#8217;<br \/>\nELSE &#8216;U&#8217;<br \/>\nEND,<br \/>\na.a AS olA_Value_inSource,<br \/>\nb.a AS colA_Value_inTarget,<br \/>\na.c AS colC_Value_inSource,<br \/>\nb.c AS colC_Value_inTarget<br \/>\nFROM Source A<br \/>\nFULL JOIN Target B<br \/>\nON ( A.C = B.C )<br \/>\nWHERE A.C IS NULL<br \/>\nOR B.C IS NULL<br \/>\nOR Checksum(a.a, a.c) &lt;&gt; Checksum(b.a, b.c)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Problem:\u00a0How to compare tables and\/or data to\u00a0identify data changes between them? Solution:\u00a0In data migration, we often have to compare two[&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/543"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=543"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/543\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=543"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}