Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.
Replace table names, database names and fields list accordingly.
SELECT * FROM
(SELECT Max(TableName) AS TableName, FIELD1, FIELD2, FIELD3
FROM (SELECT 'HRDB1..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB1..PS_MY_RECORD
UNION ALL SELECT 'HRDB2..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB2..PS_MY_RECORD ) A
GROUP BY FIELD1, FIELD2, FIELD3
HAVING COUNT(*) = 1) A
ORDER BY 1
2 comments:
Hi,
a suggestion for proc stored
ps_TableGap 'tbl1','tbl2','fields to compare in tbl1', 'fields to compare in tbl2 (optional same as tbl1 is empty)
return errors with type as 'ADD', 'DEL','GAP'
table can be on different DB only PK must be the same
/****** Object: StoredProcedure [dbo].[ps_TableGap] Script Date: 10/03/2013 16:03:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arnaud ALLAVENA
-- Create date: 03.10.2013
-- Description: Compare tables
-- =============================================
alter 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
Hi Arnaud, Thank you for sharing the code.
Post a Comment