--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)
--盘点盘盈盘亏 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)