sql server:compare data from two tables

--Comparing data between two tables in SQL Server--Create two Tables--CREATE TABLE TableA(ID Int, PRouctName Varchar(256))GOCREATE TABLE TableB(ID Int, ProuctName Varchar(256))GOINSERT INTO TableA VALUES(1,'A')INSERT INTO TableA VALUES(2,'B')INSERT INTO TableA VALUES(3,'D')INSERT INTO TableA VALUES(4,'E')INSERT INTO TableA VALUES(5,'F')INSERT INTO TableA VALUES(6,'塗聚文')INSERT INTO TableB VALUES(1,'A')INSERT INTO TableB VALUES(2,NULL)INSERT INTO TableB VALUES(3,'C')INSERT INTO TableB VALUES(4,'F')INSERT INTO TableB VALUES(5,'D')INSERT INTO TableB VALUES(6,'塗聚文')--Get rows from TableA that are not found in TableB--SELECT ProuctName FROM TableAEXCEPTSELECT ProuctName FROM TableB--Get rows from TableB that are not found in TableA--SELECT ProuctName FROM TableBEXCEPTSELECT ProuctName FROM TableA--两个表共同不同的记录SELECT ProuctNameFROM(SELECT * FROM TableAUNION ALL  select * from TableB) dataGROUP BY ProuctNameHAVING count(*)!=2---http://blogs.msdn.com/b/ramoji/archive/2010/07/01/how-to-compare-data-between-two-tables-in-sql-server.aspxCREATE PROCEDURE CompareTableData(@SourceDB sysname,@SourceSchema sysname,@SourceTable sysname,@TargetDB sysname,@TargetSchema sysname,@TargetTable sysname)ASBEGINSET NOCOUNT ONDECLARE @SQL NVarchar(Max)DECLARE @ColList Varchar(Max)--Concatenate the column list by excluding the data types that can't be used in comparision--SET @SQL = 'SELECT @ColList = CASE WHEN @ColList IS NULL THEN '''' ELSE @ColList + '','' END + SC.Name FROM ' + @SourceDB + '.sys.columns SC 'SET @SQL = @SQL + ' INNER JOIN ' + @SourceDB + '.sys.Types ST ON SC.system_type_id = ST.system_type_id WHERE object_id = object_id(@SourceTable) 'SET @SQL = @SQL + ' AND ST.Name NOT IN (''xml'',''Text'',''Image'',''Geometry'',''Geography'')'EXEC sp_executesql @SQL,N'@ColList varchar(Max) OUTPUT,@SourceTable sysname',@ColList OUTPUT,@Sourcetable--Get the rows that are missing from Target table--SET @SQL = 'SELECT ''' + @TargetTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTableSET @SQL = @SQL + ' EXCEPT 'SET @SQL = @SQL + ' SELECT ''' + @TargetTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @TargetDB + '.' + @TargetSchema + '.' + @TargetTableEXEC sp_executesql @SQL,N'@ColList varchar(Max)',@ColList--Get the rows that are missing from Source table--SET @SQL = ' SELECT ''' + @SourceTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @TargetDB + '.' + @TargetSchema + '.' + @TargetTableSET @SQL = @SQL + ' EXCEPT 'SET @SQL = @SQL + 'SELECT ''' + @SourceTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTableEXEC sp_executesql @SQL,N'@ColList varchar(Max)',@ColListEND-- =============================================-- Author:       Arnaud ALLAVENA-- Create date: 03.10.2013-- Description: Compare tables-- =============================================create PROCEDURE [dbo].[ps_TableGap]    -- Add the parameters for the stored procedure here    @Tbl1 as varchar(100),@Tbl2 as varchar(100),@Fld1 as varchar(1000), @Fld2 as varchar(1000)= ''ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;--Variables--@Tbl1 = table 1--@Tbl2 = table 2--@Fld1 = Fields to compare from table 1--@Fld2 Fields to compare from table 2Declare @SQL varchar(8000)= '' --SQL statementsDeclare @nLoop int = 1 --loop counterDeclare @Pk varchar(1000)= '' --primary key(s) Declare @Pk1 varchar(1000)= '' --first field of primary keydeclare @strTmp varchar(50) = '' --returns value in Pk determinationdeclare @FldTmp varchar (1000) = '' --temporarily fields for alias calculation--If @Fld2 empty we take @Fld1--fields rules: fields to be compare must be in same order and type - always returns GapIf @Fld2 = '' Set @Fld2 = @Fld1--Change @Fld2 with Alias prefix xxx become _xxx while charindex(',',@Fld2)>0begin    Set @FldTmp = @FldTmp + (select substring(@Fld2,1,charindex(',',@Fld2)-1) + ' as _' + substring(@Fld2,1,charindex(',',@Fld2)-1) + ',')    Set @Fld2 = (select ltrim(right(@Fld2,len(@Fld2)-charindex(',',@Fld2))))endSet @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2Set @Fld2 = @FldTmp--Determinate primary key jointure--rule: same pk in both tablesSet @nLoop = 1Set @SQL = 'Declare crsr cursor for select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = ''' + @Tbl1 + ''' or TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1 +  ''' or TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME = ''' + @Tbl1  + ''' order by ORDINAL_POSITION'exec(@SQL)open crsr fetch next from crsr into @strTmpwhile @@fetch_status = 0begin     if @nLoop = 1     begin         Set @Pk = 's.' + @strTmp + ' = b._' + @strTmp        Set @Pk1 = @strTmp        set @nLoop = @nLoop + 1     end     Else    Set @Pk = @Pk + ' and s.' + @strTmp + ' = b._' + @strTmpfetch next from crsr into @strTmp end close crsrdeallocate crsr--SQL statement buildset @SQL = 'select case when s.' + @Pk1 + ' is null then ''Del'' when b._' + @Pk1 + ' is null then ''Add'' else ''Gap'' end as TypErr, '''set @SQL = @SQL + @Tbl1 +''' as Tbl1, s.*, ''' + @Tbl2 +''' as Tbl2 ,b.* from (Select ' + @Fld1 + ' from ' + @Tbl1set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld2 + ' from ' + @Tbl2 + ')s full join (Select ' + @Fld2 + ' from ' + @Tbl2 set @SQL = @SQL + ' EXCEPT SELECT ' + @Fld1 + ' from ' + @Tbl1 +')b on '+ @Pk --Run SQL statementExec(@SQL)END---insert into TableAselect * from TableBexceptselect * from TableA--http://searchsqlserver.techtarget.com/tip/Compare-two-SQL-Server-tables-with-stored-proceduresIF Exists(Select id from sysobjects where id = object_id('sp_Compare2Tables') and type ='P') Drop Procedure sp_Compare2TablesGO-- sp_Compare2Tables            --                       -- The SP compares the structure & data in 2 tables.             -- Tables can be from different servers, different databases or different schemas.      ---------------------------------------------------------------------------------------------------------------- Parameters:                    -- 1. @Table1 - Name of the table to be checked.             -- 2. @Table2 - Name of the table to be checked.             -- 3. @ListDiff - Bit to list the differences             -- 4. @StructureOnly - Bit to compare only the structure           --                      -- Assumptions:  The length of the dynamic string should not exceed 4000 characters     --    Both tables have primary keys             --    Primary key combination is same for both tables         -- Paramenter 1, 2: Table name (With optional server name, database name, Schema name seperated with .)  --     Eg. Preethi.Inventory.Dbo.TranHeader, Preethi.Test.dbo.Tran       --        Any of the first 3 parts could be omitted.          --        Inventory.DBO.TranHeader, INV.TranHeader and TranHeader are valid     -- Parameter 3: List the differences               --    IF True it will list all the different fields (in case of structural difference)  --     or all the different entries (in case of data differences)      --     Default is 1 (List the differences)          -- Parameter 4: Compare only the structure             --     Default=0 (Compare data & structure)          ----------------------------------------------------------------------------------------------------------------                      --       Created by G.R.Preethiviraj Kulasingham B.Sc., MCP      --         Written on  : August 29, 2002         --        Modified on: September 05, 2002         --                      --------------------------------------------------------------------------------------------------------------CREATE PROC sp_Compare2Tables@TableName1 sysName ,@TableName2 sysName ,@ListDiff bit = 1 ,@StructureOnly bit =0ASSET NOCOUNT ONSET ANSI_WARNINGS ONSET ANSI_NULLS ONdeclare @SQLStr nvarchar(4000), @OrderBy varchar(4000), @ConditionList nvarchar(4000), @FieldList nvarchar(4000)declare @SvrName1 sysname, @DBName1 sysname, @Schema1 Sysname, @Table1 Sysname Declare @SvrName2 sysname, @DBName2 sysname, @Schema2 sysname, @Table2 sysnamedeclare @Int1 int, @Int