sql server:compare data from two tables

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721

--Comparing data between two tables in SQL Server --Create two Tables-- CREATE TABLE TableA(ID Int, ProuctName Varchar(256)) GO CREATE TABLE TableB(ID Int, ProuctName Varchar(256)) GO INSERT 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 TableA EXCEPT SELECT ProuctName FROM TableB   --Get rows from TableB that are not found in TableA-- SELECT ProuctName FROM TableB EXCEPT SELECT ProuctName FROM TableA   --两个表共同不同的记录 SELECT ProuctName FROM (SELECT * FROM TableA UNION ALL  select * from TableB) data GROUP BY ProuctName HAVING count(*)!=2       ---http://blogs.msdn.com/b/ramoji/archive/2010/07/01/how-to-compare-data-between-two-tables-in-sql-server.aspx CREATE PROCEDURE CompareTableData   (   @SourceDB sysname   ,@SourceSchema sysname   ,@SourceTable sysname   ,@TargetDB sysname   ,@TargetSchema sysname   ,@TargetTable sysname   )   AS   BEGIN   SET NOCOUNT ON   DECLARE @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 + '.' + @SourceTable   SET @SQL = @SQL + ' EXCEPT '   SET @SQL = @SQL + ' SELECT ''' + @TargetTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @TargetDB + '.' + @TargetSchema + '.' + @TargetTable   EXEC 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 + '.' + @TargetTable   SET @SQL = @SQL + ' EXCEPT '   SET @SQL = @SQL + 'SELECT ''' + @SourceTable + ''' as RowMissingFrom,' + @ColList + ' FROM ' + @SourceDB + '.' + @SourceSchema + '.' + @SourceTable   EXEC sp_executesql @SQL,N'@ColList varchar(Max)',@ColList   END     -- ============================================= -- 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)= '' AS BEGIN     -- 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 2 Declare @SQL varchar(8000)= '' --SQL statements Declare @nLoop int = 1 --loop counter Declare @Pk varchar(1000)= '' --primary key(s) Declare @Pk1 varchar(1000)= '' --first field of primary key declare @strTmp varchar(50) = '' --returns value in Pk determination declare @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 Gap If @Fld2 = '' Set @Fld2 = @Fld1   --Change @Fld2 with Alias prefix xxx become _xxx while charindex(',',@Fld2)>0 begin     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)))) end Set @FldTmp = @FldTmp + @Fld2 + ' as _' + @Fld2 Set @Fld2 = @FldTmp   --Determinate primary key jointure --rule: same pk in both tables Set @nLoop = 1 Set @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 @strTmp while @@fetch_status = 0 begin     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._' + @strTmp fetch next from crsr into @strTmp   end close crsr deallocate crsr   --SQL statement build set @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 ' + @Tbl1 set @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 statement Exec(@SQL) END   --- insert into TableA select * from TableB except select * from TableA   --http://searchsqlserver.techtarget.com/tip/Compare-two-SQL-Server-tables-with-stored-procedures   IF Exists(Select id from sysobjects where id = object_id('sp_Compare2Tables') and type ='P')  Drop Procedure sp_Compare2Tables GO   -- 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 =0   AS   SET NOCOUNT ON SET ANSI_WARNINGS ON SET ANSI_NULLS ON   declare @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 sysname declare @Int1 int, @Int2 int, @Int3 int, @Int4 int --Declare @TimeStamp bit     set @TableName1= RTRIM(LTRIM(@TableName1)) set @Table1 = @TableName1 set @SvrName1 = @@SERVERNAME Set @DBName1 = DB_NAME() set @Schema1 = CURRENT_USER   set @TableName2= RTRIM(LTRIM(@TableName2)) set @Table2 = @TableName2 set @SvrName2 = @@SERVERNAME Set @DBName2 = DB_NAME() set @Schema2 = CURRENT_USER   -- Check for the existance of specified Servers, databases, schemas and tables IF CHARINDEX('.',@Table1) > 0 BEGIN  set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)  set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))  IF CHARINDEX('.',@Table1) > 0  BEGIN   Set @DBName1=@Schema1   set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)   set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))   IF CHARINDEX('.',@Table1) > 0   BEGIN    SET @SvrName1=@DBName1    Set @DBName1=@Schema1    set @Schema1 = LEFT(@Table1,CHARINDEX('.',@Table1)-1)    set @Table1 = RIGHT(@Table1,LEN(@Table1)-CHARINDEX('.',@Table1))   END  END END   IF LEFT(@SvrName1,1)='['  AND RIGHT(@SvrName1,1)=']'  SET @SvrName1=SUBSTRING(@SvrName1, 2, LEN(@SvrName1)-2) IF LEFT(@DBName1,1)='[' AND RIGHT(@DBName1,1)=']'  SET @DBName1=SUBSTRING(@DBName1, 2, LEN(@DBName1)-2) IF LEFT(@Schema1,1)='['  AND RIGHT(@Schema1,1)=']'  SET @Schema1=SUBSTRING(@Schema1, 2, LEN(@Schema1)-2) IF LEFT(@Table1,1)='[' AND RIGHT(@Table1,1)=']'  SET @Table1=SUBSTRING(@Table1, 2, LEN(@Table1)-2)     IF @SvrName1<>@@SERVERNAME  IF not exists (select * FROM master.dbo.sysservers where srvname = @SvrName1)  BEGIN   PRINT 'There is no linked server named '+@SvrName1+'. Termination of Procedure.'   RETURN  END   set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName1+'].master.dbo.sysdatabases where name ='''+ @DBName1+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount=0 BEGIN  PRINT 'There is no database named '+@DBName1+'. Termination of Procedure.'  RETURN END   set @SQLStr = 'Select name INTO #TempTable FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysusers where name ='''+ @Schema1+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount=0 BEGIN  PRINT 'There is no schema named '+@Schema1+' in the specified Database. Termination of Procedure.'  RETURN END   set @SQLStr = 'Select o.name into #TempTable FROM   ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects O,   ['+@SvrName1+'].['+@DBName1+'].dbo.sysusers U   Where O.uid=U.Uid and U.Name =''' + @Schema1 +'''   and O.name=''' +@Table1+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount = 0 BEGIN  PRINT 'There is no Table named '+@Table1+'. END of work.'  RETURN END     IF CHARINDEX('.',@Table2) > 0 BEGIN  set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)  set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))  IF CHARINDEX('.',@Table2) > 0  BEGIN   Set @DBName2=@Schema2   set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)   set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))   IF CHARINDEX('.',@Table2) > 0   BEGIN    SET @SvrName2=@DBName2    Set @DBName2=@Schema2    set @Schema2 = LEFT(@Table2,CHARINDEX('.',@Table2)-1)    set @Table2 = RIGHT(@Table2,LEN(@Table2)-CHARINDEX('.',@Table2))   END  END END     IF LEFT(@SvrName2,1)='['  AND RIGHT(@SvrName2,1)=']'  SET @SvrName2=SUBSTRING(@SvrName2, 2, LEN(@SvrName2)-2) IF LEFT(@DBName2,1)='[' AND RIGHT(@DBName2,1)=']'  SET @DBName2=SUBSTRING(@DBName2, 2, LEN(@DBName2)-2) IF LEFT(@Schema2,1)='['  AND RIGHT(@Schema2,1)=']'  SET @Schema2=SUBSTRING(@Schema2, 2, LEN(@Schema2)-2) IF LEFT(@Table2,1)='[' AND RIGHT(@Table2,1)=']'  SET @Table2=SUBSTRING(@Table2, 2, LEN(@Table2)-2)   IF @SvrName2<>@@SERVERNAME  IF not exists (select * FROM master.dbo.sysservers where srvname = @SvrName2)  BEGIN   PRINT 'There is no linked server named '+@SvrName2+'. Termination of Procedure.'   RETURN  END   set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName2+'].master.dbo.sysdatabases where name ='''+ @DBName2+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount=0 BEGIN  PRINT 'There is no database named '+@DBName2+'. Termination of Procedure.'  RETURN END   set @SQLStr = 'Select name into #TempTable FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysusers where name ='''+ @Schema2+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount=0 BEGIN  PRINT 'There is no schema named '+@Schema2+'in the specIFied Database. Termination of Procedure.'  RETURN END   set @SQLStr = 'Select o.name into #TempTable FROM    ['+@SvrName2+'].['+@DBName2+'].dbo.sysobjects O,    ['+@SvrName2+'].['+@DBName2+'].dbo.sysusers U    Where O.uid=U.Uid and U.Name =''' + @Schema2 +'''    and O.name=''' +@Table2+'''' EXECUTE sp_ExecuteSQL @SQLStr IF @@rowcount = 0 BEGIN  PRINT 'There is no Table named '+@Table2+'. END of work.'  RETURN END   -- Check whether both tables are same. IF (@SvrName1 + @DbName1 + @Schema1 + @Table1)=(@SvrName2 + @DbName2 + @Schema2 + @Table2) BEGIN  PRINT 'Both Tables  should be different. Termination of Procedure'  RETURN END -- Check whether the structure of both tables are same. -- Method:  Get the tables with column data --   Select the no of rows in each and in union. --   If both are same they are same Print 'Comparing the structure started at '+Convert(varchar(35), GetDate(),109) Create Table #TableColumns (     TABLE_SERVER sysname NOT NULL,     TABLE_CATALOG sysname NOT NULL,     TABLE_SCHEMA sysname NOT NULL,     TABLE_NAME sysname NOT NULL,     COLUMN_NAME sysname NOT NULL,     ORDINAL_POSITION smallint NOT NULL,     DATA_TYPE sysname NOT NULL,     CHARACTER_MAXIMUM_LENGTH int NULL,     CHARACTER_OCTET_LENGTH int NULL,     NUMERIC_PRECISION tinyint NULL,     NUMERIC_PRECISION_RADIX smallint NULL,     NUMERIC_SCALE int NULL,      DATETIME_PRECISION smallint NULL    )    Create Table #Table_Index  (  ColumnName sysname NOT NULL,  OrderID   Int NOT NULL )    Create Table #ROWCount_Table (  Int1 int NOT NULL,  Int2 int NULL,  Int3 int NULL,  Int4 int NULL )       set @SQLStr = 'Insert into  #TableColumns SELECT '''+@SvrName1+''', '''+@DBName1 +''',     usr.name, obj.name,     Col.name,     col.colid,     spt_dtp.LOCAL_TYPE_NAME,     convert(int, OdbcPrec(col.xtype, col.length, col.xprec)  + spt_dtp.charbin),     convert(int, spt_dtp.charbin +      case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')     then  2*OdbcPrec(col.xtype, col.length, col.xprec)     else  OdbcPrec(col.xtype, col.length, col.xprec)      end),     nullif(col.xprec, 0),     spt_dtp.RADIX,     col.scale,     spt_dtp.SQL_DATETIME_SUB FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysobjects obj,  ['+@SvrName1+'].master.dbo.spt_datatype_info spt_dtp,  ['+@SvrName1+'].['+@DBName1 +'].dbo.systypes typ,  ['+@SvrName1+'].['+@DBName1 +'].dbo.sysusers usr,  ['+@SvrName1+'].['+@DBName1 +'].dbo.syscolumns col     WHERE  obj.id = col.id      AND obj.uid=usr.uid  AND typ.xtype = spt_dtp.ss_dtype  AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)  AND obj.xtype in (''U'', ''V'')  AND col.xusertype = typ.xusertype  AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)  AND obj.name =''' + @Table1+ ''' and usr.name ='''+@Schema1+'''' EXECUTE sp_ExecuteSQL @SQLStr   set @SQLStr = 'Insert into  #TableColumns SELECT '''+@SvrName2+''', '''+@DbName2 +''',     usr.name, obj.name,     Col.name,     col.colid,     spt_dtp.LOCAL_TYPE_NAME,     convert(int, OdbcPrec(col.xtype, col.length, col.xprec)  + spt_dtp.charbin),     convert(int, spt_dtp.charbin +      case when spt_dtp.LOCAL_TYPE_NAME in (''nchar'', ''nvarchar'', ''ntext'')     then  2*OdbcPrec(col.xtype, col.length, col.xprec)     else  OdbcPrec(col.xtype, col.length, col.xprec)      end),     nullif(col.xprec, 0),     spt_dtp.RADIX,     col.scale,     spt_dtp.SQL_DATETIME_SUB FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysobjects obj,  ['+@SvrName2+'].master.dbo.spt_datatype_info spt_dtp,  ['+@SvrName2+'].['+@DBName2 +'].dbo.systypes typ,  ['+@SvrName2+'].['+@DBName2 +'].dbo.sysusers usr,  ['+@SvrName2+'].['+@DBName2 +'].dbo.syscolumns col     WHERE  obj.id = col.id      AND obj.uid=usr.uid  AND typ.xtype = spt_dtp.ss_dtype  AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)  AND obj.xtype in (''U'', ''V'')  AND col.xusertype = typ.xusertype  AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)  AND obj.name =''' + @Table2+ ''' and usr.name ='''+@Schema2+''''   EXECUTE sp_ExecuteSQL @SQLStr   IF EXISTS(SELECT COLUMN_NAME,    DATA_TYPE,    CHARACTER_MAXIMUM_LENGTH,    CHARACTER_OCTET_LENGTH,    NUMERIC_PRECISION,    NUMERIC_PRECISION_RADIX,    NUMERIC_SCALE,    DATETIME_PRECISION,    COUNT(*) AS  NUMBERS   FROM #TableColumns   GROUP BY COLUMN_NAME,    DATA_TYPE,    CHARACTER_MAXIMUM_LENGTH,    CHARACTER_OCTET_LENGTH,    NUMERIC_PRECISION,    NUMERIC_PRECISION_RADIX,    NUMERIC_SCALE,    DATETIME_PRECISION   HAVING COUNT(*)=1) BEGIN  PRINT 'The Structure of the tables are different. Termination of Procedure.'  IF @ListDiff =1  SELECT A.*  FROM #TableColumns A,     (SELECT COLUMN_NAME,    DATA_TYPE,    CHARACTER_MAXIMUM_LENGTH,    CHARACTER_OCTET_LENGTH,    NUMERIC_PRECISION,    NUMERIC_PRECISION_RADIX,    NUMERIC_SCALE,    DATETIME_PRECISION,    COUNT(*) as NUMBERS   FROM #TableColumns   GROUP BY COLUMN_NAME,    DATA_TYPE,    CHARACTER_MAXIMUM_LENGTH,    CHARACTER_OCTET_LENGTH,    NUMERIC_PRECISION,    NUMERIC_PRECISION_RADIX,    NUMERIC_SCALE,    DATETIME_PRECISION   HAVING COUNT(*) =1) B  WHERE A.COLUMN_NAME = B.COLUMN_NAME AND   A.DATA_TYPE = B.DATA_TYPE AND   (ISNULL(A.CHARACTER_MAXIMUM_LENGTH,0)=ISNULL(B.CHARACTER_MAXIMUM_LENGTH,0)) AND   (ISNULL(A.NUMERIC_PRECISION, 0)=ISNULL(B.NUMERIC_PRECISION,0)) AND   (ISNULL(A.NUMERIC_PRECISION_RADIX, 0)=ISNULL(B.NUMERIC_PRECISION_RADIX,0)) AND   (ISNULL(A.NUMERIC_SCALE, 0)=ISNULL(B.NUMERIC_SCALE,0)) AND   (ISNULL(A.DATETIME_PRECISION, 0)=ISNULL(B.DATETIME_PRECISION,0))  ORDER BY A.ORDINAL_POSITION    DROP TABLE  #ROWCount_Table  DROP TABLE  #TableColumns  Print 'Comparing the structure completed at '+Convert(varchar(35), GetDate(),109)  RETURN END IF @StructureOnly=1 BEGIN  DROP TABLE  #ROWCount_Table  DROP TABLE  #TableColumns  RETURN END     ----------------------------------------------------------------------------------------- -- Check for the presence of timestamp column ----------------------------------------------------------------------------------------- -- NOTE:  This First Method is a simple method to check Whether Both Tables are Identitical.   Print 'Comparing the data started at '+Convert(varchar(20), GetDate(),114) SELECT @ConditionList='', @FieldList=''   SET @Int1=0 WHILE Exists(Select * FROM #TableColumns Where ORDINAL_POSITION>@Int1 and DATA_Type<>'TIMESTAMP' and   TABLE_SERVER = @SvrName1 AND TABLE_CATALOG = @DBName1 and TABLE_Schema =@Schema1 and TABLE_Name= @Table1) SELECT  @FieldList=@FieldList+', T.'+COLUMN_NAME,   @Int1 = ORDINAL_POSITION,   @ConditionList=@ConditionList +' AND (T.'+COLUMN_NAME+ ' = A.'+COLUMN_NAME+' OR (T.'+COLUMN_NAME+' IS NULL AND A.'+COLUMN_NAME+' IS NULL))' FROM  #TableColumns WHERE TABLE_SERVER = @SvrName1 AND   TABLE_CATALOG = @DBName1 and   TABLE_Schema =@Schema1 and   TABLE_Name= @Table1 and   ORDINAL_POSITION = (Select MIN(ORDINAL_POSITION)       FROM #TableColumns       WHERE  ORDINAL_POSITION>@Int1 and        DATA_Type<>'TIMESTAMP' and        TABLE_SERVER = @SvrName1 and        TABLE_CATALOG=@DBName1 and        TABLE_Schema =@Schema1 and        TABLE_Name= @Table1)   SET @FieldList= SUBSTRING(@FieldList, 3, LEN(@FieldList)-2) SET @ConditionList= SUBSTRING(@ConditionList, 5, LEN(@ConditionList)-4)   -- Code to find the primary key should be placed here. SET @SQLStr=' Insert Into #Table_Index (ColumnName, OrderID) select C.Name, k.keyno  from ['+@SvrName1+'].['+@DbName1+'].dbo.sysobjects O,  ['+@SvrName1+'].['+@DbName1+'].dbo.sysindexes I,  ['+@SvrName1+'].['+@DbName1+'].dbo.sysindexkeys K,  ['+@SvrName1+'].['+@DbName1+'].dbo.syscolumns C,  ['+@SvrName1+'].['+@DbName1+'].dbo.sysusers U where O.uid = u.uid and u.name = '''+@Schema1+''' and O.name ='''+@Table1+''' and I.id = O.id and I.indid=1 and I.indid = k.indid and O.id = k.id and k.colid =C.Colid and C.id =O.id ' EXECUTE sp_ExecuteSQL @SQLStr   SET @Int1=0 SET @OrderBy ='' WHILE Exists(Select * from #Table_Index Where OrderID >@Int1)  Select @OrderBy = @OrderBy+', T.'+ColumnName, @Int1=OrderID  From #Table_Index Where OrderID >@Int1 and OrderID =(    Select Min(OrderID) From #Table_Index where OrderID >@Int1) IF @OrderBy =''   --No Primary Index Found  SET @OrderBy =@FieldList ELSE  SET @OrderBy= SUBSTRING(@OrderBy, 3, LEN(@OrderBy)-2)         SET @SQLStr=' INSERT INTO #ROWCount_Table Select i.[rows],0,0, 0 FROM ['+@SvrName1+'].['+@DBName1+'].dbo.sysindexes i,  ['+@SvrName1+'].['+@DBName1+'].dbo.sysObjects o,  ['+@SvrName1+'].['+@DBName1+'].dbo.sysusers u Where o.id=i.id and u.uid = o.uid and i.indid<2 and  u.name='''+@Schema1+''' and o.name ='''+@Table1+'''    update #ROWCount_Table set Int2 =   (   Select i.[rows]   FROM ['+@SvrName2+'].['+@DBName2+'].dbo.sysindexes i,    ['+@SvrName2+'].['+@DBName2+'].dbo.sysObjects o,    ['+@SvrName2+'].['+@DBName2+'].dbo.sysusers u   Where o.id=i.id and u.uid = o.uid and i.indid<2 and    u.name='''+@Schema1+''' and o.name ='''+@Table1+''')   Update #ROWCount_Table Set Int3=   (   Select Count(1) FROM    (    Select '+ @FieldList +'    FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T    UNION    Select '+ @FieldList +'    FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T    ) A   ) Update #ROWCount_Table Set Int4=   (   Select Count(1) FROM    (    Select '+ @OrderBy +'    FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T    UNION    Select '+ @OrderBy +'    FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T    ) A   )' EXECUTE sp_ExecuteSQL @SQLStr    Select @Int1=Int1, @Int2=Int2, @Int3=Int3, @Int4=Int4 FROM #ROWCount_Table IF @Int1=@Int3 and @Int2=@Int3 BEGIN  PRINT 'Both Tables are identitical.'  DROP TABLE  #ROWCount_Table  DROP TABLE  #TableColumns  Print 'Comparing the data completed at '+Convert(varchar(35), GetDate(),109)  RETURN END   PRINT ' Both Tables are having different data ------------------------------------------------------ No. of records in '+@TableName1+ ' are '+Convert(Varchar(20), @Int1)+'. No. of records in '+@TableName2+ ' are '+Convert(Varchar(20), @Int2)+'. No. of records common in both are '+Convert(Varchar(20), @Int1+@int2-@Int3)+'. No. of unmatched records in '+@TableName1+ ' are '+Convert(Varchar(20),@int3-@Int2)+'. No. of unmatched records in '+@TableName2+ ' are '+Convert(Varchar(20),@int3-@Int1)+'.   No. of New records in '+@TableName1+ ' are '+Convert(varchar(20), @Int4-@Int2)+'. No. of New records in '+@TableName2+ ' are '+Convert(varchar(20), @Int4-@Int1)+'. No. of modified but existing records are '+Convert(varchar(20), @Int3-@Int4)+'. ------------------------------------------------------   Comparing the data step 1 completed at '+Convert(varchar(35), GetDate(),109) IF @ListDiff = 0 BEGIN  DROP TABLE #Table_Index  DROP TABLE  #ROWCount_Table  DROP TABLE  #TableColumns  RETURN END ------------------------------------------------------------------------------------------ --   Now the Tables are not identitical. Now List all the Rows that are different   -- ------------------------------------------------------------------------------------------   set @SQLStr='Select '''+@TableName2+''' TABLE_NAME, '+@FieldList +' FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T, (  Select '+ @FieldList+', SUM(Counter) as Instances FROM   (Select '+@FieldList+ ', 1 as Counter FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T   UNION ALL   Select '+ @FieldList+', 1 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T   ) T  Group By '+ @FieldList + ' Having SUM(Counter)<2  )a  WHERE '+@ConditionList +' UNION ALL Select '''+@TableName1+''' TABLE_NAME, '+@FieldList +' FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T, (  Select '+ @FieldList+', SUM(Counter) as Instances FROM   (Select '+@FieldList+ ', 1 as Counter FROM ['+@SvrName1+'].['+@DBName1+'].['+@Schema1+'].['+@Table1+'] T   UNION ALL   Select '+ @FieldList +', 1 FROM ['+@SvrName2+'].['+@DBName2+'].['+@Schema2+'].['+@Table2+'] T   ) T  Group By '+ @FieldList + ' Having SUM(Counter)<2  ) A  WHERE '+@ConditionList+' Order By '+ @OrderBy EXECUTE sp_ExecuteSQL @SQLStr   DROP TABLE #Table_Index   DROP TABLE  #ROWCount_Table DROP TABLE  #TableColumns PRINT 'Comparing the data step 2 completed at '+Convert(varchar(35), GetDate(),109)

　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

--盘点盘盈盘亏 14601500001762（在借圖書） DECLARE @inventoryLastDay DATETIME,@t INT,@t1 INT,@t2 INT,@t3 INT SET @inventoryLastDay='2015-08-31' DROP TABLE #temp DROP TABLE #temp1 DROP TABLE #temp2 DROP TABLE #temp3 DROP TABLE #temp4 DROP TABLE #temp5   SELECT  BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoName,BookInfoID INTO #temp FROM View_BookInfoList WHERE BookInfoAddDate<=@inventoryLastDay   SELECT BookInventoryBarCode AS BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoName,BookInventoryInfoID AS BookInfoID INTO #temp1 FROM View_InventoryBookList WHERE BookInventoryDate<=@inventoryLastDay   select BookLendingInfoBarCode AS BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoName,BookLendingInfoID AS BookInfoID INTO #temp2 FROM View_BookLending WHERE BookLendingStar<=@inventoryLastDay -- SELECT *   INTO  #temp3  FROM #temp1 UNION SELECT * FROM #temp2 -- SELECT @t=COUNT(*) FROM #temp SELECT @t1=COUNT(*) FROM #temp1 SELECT @t2=COUNT(*) FROM #temp2 SELECT @t3=COUNT(*) FROM #temp3   SELECT BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoID INTO #temp4 FROM #temp EXCEPT SELECT BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoID FROM #temp3     SELECT BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoID INTO #temp5 FROM #temp3 EXCEPT SELECT BookInfoBarCode,BookInfoISBN,BookUseCode,BookInfoID FROM #temp   SELECT * FROM #temp3 EXCEPT SELECT * FROM #temp1  ORDER BY BookInfoID   SELECT * FROM #temp2 ORDER BY BookInfoID   SELECT * FROM #temp SELECT * FROM #temp1  --1796 SELECT * FROM #temp2  --70 SELECT * FROM #temp3  --1865 SELECT * FROM #temp4  --22 SELECT * FROM #temp5  --0 SELECT @t,@t1,@t2,@t3     --Fc0023.Fd0005.Bl0017 SELECT * FROM BookInfoList WHERE BookUseCode='Fc0023'   SELECT * FROM BookInfoList WHERE BookUseCode='Fd0005'   SELECT * FROM BookInfoList WHERE BookUseCode='Bl0017'   --1690,1669,1785 ,青春咖啡馆,你的孤独虽败犹荣,秘密   SELECT * FROM BookLendingList  WHERE BookLendingInfoID IN(1690,1669,1785) SELECT * FROM  InventoryBookList WHERE  BookInventoryInfoID IN(1690,1669,1785) SELECT * FROM  InventoryLossesProfit WHERE  LossesProfitInfoID IN(1690,1669,1785)