March 18, 2005 at 2:49 pm
None of the codes I received worked.
I have created a mock table and here are the fields: f_name,l_name,addr
How do I check from 2 identical tables if the value of one of the fields has changed?
I need help badly
March 20, 2005 at 7:50 am
I use a script that is something like:
select
sum(case when a.field1 <> b.field1 then 1 else 0 end) cntErrfield1,
sum(case when a.field2 <> b.field2 then 1 else 0 end) cntErrfield2,...
from tbl1 as a join tbl2 as b
on a.PK = b.PK
This will tell me which fields dont match - i can then go back and QA only the ones that are greater than 0. Sometimes nulls are an issue - in that case you would need to incorporate some sort null conversion.
March 20, 2005 at 10:47 am
I use a combination of the following three methods:
SELECT SUM(CTR),[ID] FROM
(SELECT 1 AS CTR,* FROM Compare1
UNION
SELECT 1 AS CTR,* FROM Compare2) AS X
GROUP BY [ID]
HAVING SUM(CTR)>1
SELECT a.[ID]
FROM Compare1 AS a
LEFT OUTER JOIN Compare2 AS b ON a.[ID]=b.[ID]
WHERE b.[ID] IS NULL
SELECT a.[ID]
FROM Compare2 AS a
LEFT OUTER JOIN Compare1 AS b ON a.[ID]=b.[ID]
WHERE b.[ID] IS NULL
The first statement returns the primary keys for the rows where they exist in both tables but the field contents are somehow different (UNION is implicitly DISTINCT).
The second and third statements are just mirrors and they return keys for rows that exist in the first table but not the second.
You can probably just plug your table and primary key column names in and run this verbatim.
March 21, 2005 at 4:20 am
Hi,
I found this source in http://www.sqlteam.com. You may solve your problem easily.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE CompareTables(@table1 varchar(100), @table2 Varchar(100),
@T1ColumnList varchar(1000), @T2ColumnList varchar(1000) = '')
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
---- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.
declare @sql varchar(8000)
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @sql = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList +
' FROM ' + @Table2
exec ( @sql)
set @sql = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @sql + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
print @sql
exec ( @sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Yours SQL Friend
March 22, 2005 at 8:05 am
HI,
We use to use a combination of scripts, but the logistics and management of these became overwhelming. We now use a third party tool called SQL Compare from Redgate. I don't normally do advertisements, but this is definitely one tool, and well worth its price, that should be in each DBA's toolbox!
Many thanks. Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply