Comparison of two tables

  • Murad_J30

    SSC Enthusiast

    Points: 160

    Hello,

    I am writing to all of you to get some infomation regarding comparison of two tables.

    The question is:

    Are theere any ways to compare data of two tables if the structure are a little different ?

    There is a script which does all of these, but the problem is that it does not do data comparison if a structures are different.

    Tables that I would like to compare are almost sructurely same, (The Name of columns), but in one of them there have some defference like lenght of filed or Type ...

    How can I compare the tables I have mentioned above ?

    Thanks,

    Murad.

  • Melville

    SSCommitted

    Points: 1985

    Murad

    Why not try casting the fields in a lowest common denominator kind of way to compare them.

    This kind of idea...

    select

    t1.key1,

    t1.val1 as table1_val1,

    t2.val1 as table2_val1

    from

    table1 t1

    inner join table2 t2 on t1.key1=t2.key1

    where

    cast(t1.val1 as varchar(1000))=cast(t2.val1 as varchar(1000))

  • Murad_J30

    SSC Enthusiast

    Points: 160

    Thanks for your reply.

    What if I do not know the exact column to compare and I would like to compare whole column in tables ?

    Thanks,

    Murad

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    Do you have primary keys or unique keys to identify the rows uniquely?

    Is the name of the fields same?

    I can get a part from my script (Compare 2 Tables) by removing the validation part from the script.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Murad_J30

    SSC Enthusiast

    Points: 160

    Thanks a lot for your reply.

    Yes ,I have all that stuff on the tables and the name of fields are same as well.

    The only difference is that some fields have like data types or size different on some fields.

    Murad,

    Edited by - Murad_J30 on 06/17/2003 01:16:03 AM

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    I am including the a part of the code from sp_Compare2Tables

    (removed part is actually checking the structure)

    I didn't check the code throughly, sa there may be errors.

    In case of emergency please contact me on preethi@softlogic.lk

    Now the code copy the rest into query analyzer and run in master then run with the values

    wish you all the best.

    Preethi

    (G.R.Preethiviraj Kulasingham)

    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 could 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 --

    -- 5. @CheckTimeStamp - Bit to check the timestampfields too --

    -- 6. @Verbose - Bit To Print the Queries Used --

    -- 7. @Fields - Optional List of fields which to be checked --

    -- 8. @SeperateLists Bit to return seperate results of New, Updated and Deleted Entries (@Table1 is Standard) --

    -- --

    -- Assumptions: The length of the field list and other dynamic strings should not exceed 8000 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 --

    -- Note: --

    -- When using multi part name include them in Single Quotations --

    -- (Eg. 'Inventory.DBO.TranHeader', 'INV.TranHeader') --

    -- 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 structure & data -if structure is same.) --

    -- Parameter 5: Check timestamp fields --

    -- Default =0 (Ignore timestamp columns) --

    -- Parameter 6: Verbose Mode (Print the queries too --

    -- Default =0 (Donot print the queries) --

    -- Parameter 7: List of fields which to be checked --

    -- If omitted, all fields (except timestamp) will be checked unless parameter 5 is set --

    -- Parameter 8: Return seperate results for inserted, Updated and deleted

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

    -- --

    -- Created by G.R.Preethiviraj Kulasingham B.Sc., MCP --

    -- Written on : August 17, 2002 --

    -- Modified on : December 06, 2002 --

    -- --

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

    CREATE PROC sp_Compare2Tables

    @TableName1 sysName ,

    @TableName2 sysName ,

    @ListDiff bit = 1 ,

    @StructureOnly bit =0 ,

    @CheckTimeStamp bit =0 ,

    @Verbose bit =0 ,

    @Fields varchar(4000)='',

    @SeperateLists bit =0

    AS

    SET NOCOUNT ON

    SET ANSI_WARNINGS ON

    SET ANSI_NULLS ON

    declare @SQLStr nvarchar(4000), @OrderBy varchar(4000), @ConditionList varchar(4000), @FieldList varchar(4000)

    Declare @SQL1 varchar(8000), @SQL2 varchar(8000), @SQL3 varchar(8000), @SQL4 varchar(8000)

    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 @Table1 = @TableName1

    set @SvrName1 = ISNULL(PARSENAME(@TableName1,4), @@SERVERNAME)

    Set @DBName1 = ISNULL(PARSENAME(@TableName1,3), DB_NAME())

    set @Schema1 = ISNULL(PARSENAME(@TableName1,2), CURRENT_USER)

    set @Table1= PARSENAME(@TableName1,1)

    set @SvrName2 = ISNULL(PARSENAME(@TableName2,4), @@SERVERNAME)

    Set @DBName2 = ISNULL(PARSENAME(@TableName2,3), DB_NAME())

    set @Schema2 = ISNULL(PARSENAME(@TableName2,2), CURRENT_USER)

    set @Table2 = PARSENAME(@TableName2,1)

    -- Check for the existance of specified Servers, databases, schemas and tables

    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 @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,

    IS_NULLABLE bit 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

    )

    IF @Verbose=1

    PRINT '

    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,

    IS_NULLABLE bit 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,

    col.isnullable,

    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

    IF @Verbose=1

    Print @SQLStr

    set @SQLStr = 'Insert into #TableColumns

    SELECT '''+@SvrName2+''', '''+@DbName2 +''',

    usr.name, obj.name,

    Col.name,

    col.colid,

    col.isnullable,

    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 @Verbose=1

    Print @SQLStr

    IF @Fields<>''

    Delete From #TableColumns Where CharIndex(COLUMN_NAME, @Fields)=0

    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(35), GetDate(),109)

    SELECT @ConditionList='', @FieldList=''

    IF @Fields=''

    BEGIN

    IF @CheckTimeStamp =1

    BEGIN

    IF NOT Exists(Select * FROM #TableColumns Where DATA_Type='TIMESTAMP')

    SET @CheckTimeStamp=0

    END

    IF Exists(Select * FROM #TableColumns Where (DATA_Type<>'TIMESTAMP' or @CheckTimeStamp=1 ) and

    TABLE_SERVER = @SvrName1 AND TABLE_CATALOG = @DBName1 and TABLE_Schema =@Schema1 and TABLE_Name= @Table1)

    SELECT @FieldList=@FieldList+',T.'+COLUMN_NAME,

    @ConditionList= case IS_NULLABLE

    WHEN 1 THEN @ConditionList +'AND((T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME + ')OR(T.'+COLUMN_NAME+' IS NULL AND A.'+COLUMN_NAME+' IS NULL))'

    ELSE @ConditionList +'AND(T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME +')'

    END

    FROM #TableColumns

    WHERE TABLE_SERVER = @SvrName1 AND

    TABLE_CATALOG = @DBName1 and

    TABLE_Schema =@Schema1 and

    TABLE_Name= @Table1 and

    (DATA_Type<>'TIMESTAMP' or @CheckTimeStamp=1)

    ORDER BY ORDINAL_POSITION

    END

    ELSE

    BEGIN

    IF Exists(Select * FROM #TableColumns Where CharIndex(COLUMN_NAME, @Fields)>0 and

    TABLE_SERVER = @SvrName1 AND TABLE_CATALOG = @DBName1 and TABLE_Schema =@Schema1 and TABLE_Name= @Table1)

    SELECT @FieldList=@FieldList+',T.'+COLUMN_NAME,

    @ConditionList= case IS_NULLABLE

    WHEN 1 THEN @ConditionList +'AND((T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME + ')OR(T.'+COLUMN_NAME+' IS NULL AND A.'+COLUMN_NAME+' IS NULL))'

    ELSE @ConditionList +'AND(T.'+COLUMN_NAME+ '=A.'+COLUMN_NAME +')'

    END

    FROM #TableColumns

    WHERE TABLE_SERVER = @SvrName1 AND

    TABLE_CATALOG = @DBName1 and

    TABLE_Schema =@Schema1 and

    TABLE_Name= @Table1 and

    CharIndex(COLUMN_Name, @Fields)>0

    ORDER BY ORDINAL_POSITION

    END

    SET @FieldList= SUBSTRING(@FieldList, 2, LEN(@FieldList)-1)

    SET @ConditionList= SUBSTRING(@ConditionList, 4, LEN(@ConditionList)-3)

    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.status & 0x800) = 0x800 and I.indid = k.indid and O.id = k.id and k.colid =C.Colid and C.id =O.id

    '

    EXECUTE sp_ExecuteSQL @SQLStr

    IF @Verbose=1

    Print @SQLStr

    SET @OrderBy =''

    IF Exists(Select * from #Table_Index )

    Select @OrderBy = @OrderBy+',T.'+ColumnName From #Table_Index Order By OrderID

    IF @OrderBy ='' --No Primary Index Found

    SET @OrderBy =@FieldList

    ELSE

    SET @OrderBy= SUBSTRING(@OrderBy, 2, LEN(@OrderBy)-1)

    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='''+@Schema2+''' and o.name ='''+@Table2+''')

    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

    IF @Verbose=1

    Print @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 --

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

    IF @SvrName1=@@SERVERNAME SET @SvrName1='' ELSE SET @SvrName1='['+@SvrName1+'].'

    IF @SvrName2=@@SERVERNAME SET @SvrName2='' ELSE SET @SvrName2='['+@SvrName2+'].'

    IF @SvrName1='' AND @DBName1=DB_NAME() SET @DBName1='' ELSE SET @DBName1='['+@DBName1+'].'

    IF @SvrName2='' AND @DBName2=DB_NAME() SET @DBName2='' ELSE SET @DBName2='['+@DBName2+'].'

    IF @SvrName1='' AND @DBName1='' and @Schema1=CURRENT_USER SET @Schema1='' ELSE SET @Schema1='['+@Schema1+'].'

    IF @SvrName2='' AND @DBName2='' and @Schema2=CURRENT_USER SET @Schema2='' ELSE SET @Schema2='['+@Schema2+'].'

    IF (@CheckTimeStamp=1 or @Fields<>'')

    SET @SQL2='

    Select '+ @FieldList+',SUM(Rows) as Inst FROM

    (Select '+ @FieldList+ ', 1 as Rows FROM '+@SvrName1+@DBName1+@Schema1+'['+@Table1+'] T

    UNION ALL

    Select '+ @FieldList+', 1 FROM '+@SvrName2+@DBName2+@Schema2+'['+@Table2+'] T

    ) T

    Group By '+ @FieldList + ' Having SUM(Rows)<2

    )a '

    ELSE

    SET @SQL2='

    Select '+ @FieldList+', SUM(Rows) as Inst FROM

    (Select T.*, 1 as Rows FROM '+@SvrName1+@DBName1+@Schema1+'['+@Table1+'] T

    UNION ALL

    Select T.*, 1 FROM '+@SvrName2+@DBName2+@Schema2+'['+@Table2+'] T

    ) T

    Group By '+ @FieldList + ' Having SUM(Rows)<2

    )a '

    select @SQL1=

    CASE @Fields

    WHEN '' THEN 'Select '''+@TableName2+''' TABLE_NAME,T.*

    FROM '+@SvrName2+@DBName2+@Schema2+'['+@Table2+'] T,( '

    ELSE 'Select '''+@TableName2+''' TABLE_NAME,'+@FieldList+'

    FROM '+@SvrName2+@DBName2+@Schema2+'['+@Table2+'] T,( '

    END, @ConditionList='

    WHERE '+@ConditionList

    , @SQL3=

    CASE @Fields

    WHEN '' THEN '

    UNION

    Select '''+@TableName1+''' TABLE_NAME,T.*

    FROM '+@SvrName1+@DBName1+@Schema1+'['+@Table1+'] T,('

    ELSE '

    UNION

    Select '''+@TableName1+''' TABLE_NAME,'+@FieldList+'

    FROM '+@SvrName1+@DBName1+@Schema1+'['+@Table1+'] T,('

    END,

    @SQL4='

    Order By '

    IF @Verbose=1

    PRINT @SQL1 + @SQL2 + @ConditionList + @SQL3 + @SQL2 + @ConditionList + @SQL4 + @OrderBy

    IF LEN( @SQL1 + @SQL2 + @ConditionList + @SQL3 + @SQL2 + @ConditionList + @SQL4 + @OrderBy)<=4000

    BEGIN

    SET @SQLStr = @SQL1 + @SQL2 + @ConditionList + @SQL3 + @SQL2 + @ConditionList + @SQL4 + @OrderBy

    EXECUTE sp_ExecuteSQL @SQLStr

    END

    ELSE

    EXECUTE (@SQL1 + @SQL2 + @ConditionList + @SQL3 + @SQL2 + @ConditionList + @SQL4 + @OrderBy)

    DROP TABLE #Table_Index

    DROP TABLE #ROWCount_Table

    DROP TABLE #TableColumns

    PRINT '-- Comparing the data step 2 completed at '+Convert(varchar(35), GetDate(),109)

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • Murad_J30

    SSC Enthusiast

    Points: 160

    Hello ,

    I created the SP you gave me and ran against those table. I got the message below:

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

    Server: Msg 245, Level 16, State 1, Line 18

    Syntax error converting the varchar value 'VBPCRBPXA' to a column of data type int.

    --Comparing the structure started at Jun 17 2003 2:09:07:373PM

    -- Comparing the data started at Jun 17 2003 2:09:07:827PM

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

    Could you please advise on this ?

    Thanks,

    Murad.

  • Frank Kalis

    SSC Guru

    Points: 111183

    Hi Murad,

    quote:


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

    Server: Msg 245, Level 16, State 1, Line 18

    Syntax error converting the varchar value 'VBPCRBPXA' to a column of data type int.

    --Comparing the structure started at Jun 17 2003 2:09:07:373PM

    -- Comparing the data started at Jun 17 2003 2:09:07:827PM

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


    can you post how you have called the procedure?

    I didn't get it to work here (Sorry Preethi, although I haven't tried any further since last week).

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Murad_J30

    SSC Enthusiast

    Points: 160

    This way :

    sp_Compare2Tables_upd 'maxdata1..invmatch','maxtest1..invmatch'

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    Dear Murad,

    From you message what I understand is you have a column called 'VBPCRBPXA' It is defined as integer in table1 and as varchar in table2

    The error happens when you compare an integer with varchar. Since integer is compared SQLServer tries to convert the varchar into int.

    If you change the order of the tables (sp_Compare2Tables ‘Table2’, ‘table1’ instead of sp_compare2Tables ‘Table1’, ‘table2’

    it may work. (Sorry I haven’t tried on my computer, but I feel) If you still get the same error please convert the field into varchar in both tables

    With best wishes and kind regards,

    Preethi

    (G.R.Preethiviraj Kulasingham)

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply