SQL Server 比對資料差異

標籤：

    做資料移轉或者複製時，有時需要驗證目標表和源表之間的資料差異。下面我們就介紹幾種驗證資料差異的方法：

    1.兩個表串連查詢，對比每一列的值

Declare @vSQL varchar(max)Declare @vCols varchar(max)Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)Insert into vTable1Select 1,123,‘CS‘,465 Union AllSelect 2,123,‘CS‘,345 Union AllSelect 3,223,‘TE‘,190Insert into vTable2Select 1,123,‘CS‘,465 Union AllSelect 2,223,‘TE‘,345 Union AllSelect 3,223,‘TE‘,190-- Get the column names from schema with case statements to get 0 or 1 as result-- Now, this will depend upon the columns of your actual tables-- Data approachSelect @vCols = Stuff((Select ‘,case when a.‘ + [name] + ‘ = b.‘ + [name] + ‘ then Cast(b.‘ + [name]+‘ as varchar(10)) else cast(b.‘ + [name] + ‘ as varchar(max)) + ‘‘(old)‘‘ + ‘‘ ‘‘ + Cast(a.‘ + [name] + ‘ as varchar(10)) + ‘‘(new)‘‘ end as ‘ +[name] from sys.columnswhere Object_id = Object_id(‘vTable1‘) for XML Path(‘‘)),1,1,‘‘)-- Concatenate the @vCols with main sqlSet @vSQL = ‘ Select a.id,‘ + @vCols + ‘ From vTable1 aInner Join vTable2 b on b.ID = a.ID ‘Print @vSQLExec (@vSQL)--Flag approachSelect @vCols = Stuff((Select ‘,case when a.‘ +[name] + ‘ = b.‘+ [name] + ‘ then 1 else 0 end as ‘ +[name] from sys.columnswhere Object_id= Object_id(‘vTable1‘) for XML Path(‘‘)),1,1,‘‘)Set @vSQL = ‘ Select a.id,‘ + @vCols + ‘ From vTable1 aInner Join vTable2 b on b.ID = a.ID ‘Print @vSQLExec (@vSQL)Drop table vTable1Drop table vTable2

　　

 

      2.對比兩個表的總行數

         Select count(1) from table_a

         select count(1) from table_b

         如果表上有時間欄位，用這個方式去對比也是OK的，這種方法只能看總行數是否一致。

 

     3.  用微軟提供的tablediff 工具   

         先對比兩個資料一樣的表 

truncate table FunctionMenu_t
insert into FunctionMenu_t
select * from dbo.FunctionMenu

　　 

       再對比資料不一樣的表

update top(1) FunctionMenu_t set Id =‘aaaa‘

　　

       對比結果給出了兩個表之間的不同。

      4. 用複製工具驗證資料一致性

        

      ---------------------------------------------

          Tablediff參數 

          

tablediff [ -? ] | {        -sourceserver source_server_name[\instance_name]        -sourcedatabase source_database        -sourcetable source_table_name     [ -sourceschema source_schema_name ]    [ -sourcepassword source_password ]    [ -sourceuser source_login ]    [ -sourcelocked ]        -destinationserver destination_server_name[\instance_name]        -destinationdatabase subscription_database         -destinationtable destination_table     [ -destinationschema destination_schema_name ]    [ -destinationpassword destination_password ]    [ -destinationuser destination_login ]    [ -destinationlocked ]    [ -b large_object_bytes ]     [ -bf number_of_statements ]     [ -c ]     [ -dt ]     [ -et table_name ]     [ -f [ file_name ] ]     [ -o output_file_name ]     [ -q ]     [ -rc number_of_retries ]     [ -ri retry_interval ]     [ -strict ]    [ -t connection_timeouts ] }

   -c 對比列
   -b 大資料類型要對比的位元組數，任何大於此值的大資料類型列都不會對比
   -o 輸出檔案的完整路徑和名稱。
   -q 只進行行數和架構的比較。

 

SQL Server 比對資料差異