June 16, 2003 at 1:02 am
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.
June 16, 2003 at 4:03 am
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))
June 16, 2003 at 4:41 am
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
June 17, 2003 at 1:09 am
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/
June 17, 2003 at 1:15 am
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
June 17, 2003 at 2:33 am
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/
June 17, 2003 at 3:32 am
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.
June 17, 2003 at 3:37 am
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]
June 17, 2003 at 3:44 am
This way :
sp_Compare2Tables_upd 'maxdata1..invmatch','maxtest1..invmatch'
June 17, 2003 at 5:18 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply