{"id":2934,"date":"2013-11-23T06:20:31","date_gmt":"2013-11-23T06:20:31","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2934"},"modified":"2013-11-23T06:20:31","modified_gmt":"2013-11-23T06:20:31","slug":"excepthow-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/11\/23\/excepthow-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data\/","title":{"rendered":"[Except]How to compare the rows of two tables and fetch the differential data."},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nIn this blog I would like to demonstrate a scenario where users want to move the changes between the tables in two different databases.\r\nLet&rsquo;s say we would like to compare and move the changes between the databases for some tables using T-SQL\r\nThe below example talks about moving the changes between the two databases in the same instance However the same can be extended across instances if you use linked server or SSIS packages.\r\nAlso we can write queries to move the DML changes from source to destination and vice versa. Let&rsquo;s look at the below example\r\n \r\n \r\n--creating a source database\r\ncreate database source\r\n \r\n--create source  table\r\nuse source\r\n \r\ncreate table Product(\r\n \r\nPid int  primary key ,\r\nPname varchar (10),\r\nPcost float,\r\nsource int ,\r\nlocation varchar(10))\r\n \r\n--create destination database\r\n \r\ncreate database Destination\r\n \r\n--create destination table\r\n \r\nuse Destination\r\n \r\ncreate table Product(\r\n \r\nPid int  primary key ,\r\nPname varchar (10),\r\nPcost float,\r\nsource int,\r\nlocation varchar(10) )\r\n \r\n--Insert data into source table\r\nuse source\r\n \r\ninsert into product values  ( 1,&#39;rdbms&#39;,100,200,&#39;ind&#39;)\r\ninsert into product values  ( 2,&#39;dbm&#39;,20,100,&#39;US&#39;)\r\ninsert into product values  ( 3,&#39;arp&#39;,30,250,&#39;UK&#39;)\r\ninsert into product values  ( 4,&#39;mqr&#39;,40,100,&#39;ind&#39;)\r\ninsert into product values  ( 5,&#39;ttp&#39;,50,200,&#39;us&#39;)\r\n \r\n-- EXCEPT returns any distinct values from the left query that are not also  found on the right query.\r\n--The below query gives us difference between sourec and destination\r\n-- we can use except ket word to look at selected columns or entire table\r\n \r\nselect * from source.dbo.product\r\n \r\nexcept\r\n \r\nselect * from [Destination].dbo.product\r\n \r\n--updating destination table with the changes from source\r\n \r\ninsert into [Destination].dbo.product\r\nselect * from source.dbo.product\r\nexcept\r\nselect * from [Destination].dbo.product\r\n \r\n-- We see that the destination is populated with all the rows from source\r\n \r\nselect * from [Destination].dbo.product\r\n \r\n--Now lets update the row in the source and see how it works\r\n \r\nupdate source.dbo.product\r\nset pname=&#39;sql&#39;\r\nwhere pid =1\r\n--run the below query\r\nselect * from source.dbo.product\r\n \r\nexcept\r\n \r\nselect * from [Destination].dbo.product\r\n \r\n-- the result gives us the only row which was changed in source\r\n \r\n-- loading the deiffrences to a temp table\r\nselect * into #temp from source.dbo.product\r\n \r\nexcept\r\n \r\nselect * from [Destination].dbo.product\r\n \r\n--updating the destination with changes\r\n \r\nupdate [Destination].dbo.product\r\nset [Destination].dbo.product.pname= #temp.pname\r\nfrom #temp where #temp.pid= [Destination].dbo.product.pid\r\n \r\n--lets run the statement to see the difference between these tables\r\n \r\nselect * from source.dbo.product\r\n \r\nexcept\r\n \r\nselect * from [Destination].dbo.product\r\n \r\n--lets see how the delete works\r\n \r\ndelete from source.dbo.product where pid= 2\r\n \r\n-- to see the rows which were deleted at source or inserted at destination only\r\nselect * from [Destination].dbo.product\r\nexcept\r\nselect * from source.dbo.product\r\n--based on the application logic either we will insert it back in the source or delete from dest\r\n \r\n--lets say we want to delete from dest as well ,\r\n \r\nselect * into  #temp from [Destination].dbo.product\r\nexcept\r\nselect * from source.dbo.product\r\n \r\ndelete from [Destination].dbo.product where pid in ( select pid from #temp)\r\n \r\n-- Now lets see that difference between the tables\r\nselect * from [Destination].dbo.product\r\nexcept\r\nselect * from source.dbo.product\r\n \r\n<\/pre>\n<p>\n\u6765\u81ea:<a href=\"http:\/\/blogs.msdn.com\/b\/sqlserverfaq\/archive\/2013\/11\/22\/how-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data.aspx\">How to compare the rows of two tables and fetch the differential data.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog I would like to demonstrate a scenario whe [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2934","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2934"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2934\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}