May 4, 2017 at 11:45 am
I have 2 tables. same structure. 14.2 million rows in each. 155 columns, 120 of which are varchars..
There are only several rows that have differences. usually in one or two columns only. in order to find out which columns and what values are different, I have to go through each column comparison, 155 columns drive me nuts.. i mean the resulset of a simple select from joiningn 2 tables on common PK field
consists of 310 columns, as in this SQL:
SELECT H.MRReceived_Age, H2. MRReceived_Age,
H.MRValidationDt, H2.MRValidationDt,
H.ReviewDueDate, H2.ReviewDueDate,
H.MRReceivedDt, H2.MRReceivedDt,
H.DaysToReview, H2.DaysToReview,
H.ReconReceived_Age, H2.ReconReceived_Age,
,,,,,..... + 150 more columns...,
..... FROM dbo.TEST5_H H join dbo.TEST5_H2 H2
on h.PKFiedl= h2.PKField
-- ------------------------ -- it woudl be doable/ok if there were fewer columns...
select * from dbo.TEST5_H
except
select * from dbo.TEST5_H2
shows 5 rows (that are supposedly different), but not WHICH ONES are different in each 310 column row..
so the only way i know how to fully compare rows other than using Except is
SELECT
H.MRReceived_Age, H2. MRReceived_Age,
H.MRValidationDt, H2.MRValidationDt,
H.ReviewDueDate, H2.ReviewDueDate,
H.MRReceivedDt, H2.MRReceivedDt,
H.DaysToReview, H2.DaysToReview,
H.ReconReceived_Age, H2.ReconReceived_Age,
,,,,,
..... 150 more columns...,
.....
FROM dbo.TEST5_H H join dbo.TEST5_H2 H2
on h.PKFiedl= h2.PKField
WHERE
H.MRReceived_Age <> H2. MRReceived_Age OR
H.MRValidationDt <> H2.MRValidationDt OR
H.ReviewDueDate <> H2.ReviewDueDate OR
H.MRReceivedDt <> H2.MRReceivedDt OR
H.DaysToReview <> H2.DaysToReview OR
-- ......................... OR -- etc..
-- ... and 150 more fields (most likely all text fields need to be trimmed while comparing (which adds a lot ---- of work to already tideous and time-consuming 155 fields writing H.fld <> H2.fld, ....
-- 155 times for 155 --columns in the table. and still i won't see which columns are different exactly. Cannot see that without scrolling through every row of 310 column pairs..
Can anyone PLEASE help me with this? i am so tired of col by col comparison. My eyes hurt already...
DOES a solution to my question even exist?
Likes to play Chess
May 4, 2017 at 12:27 pm
You could always use Excel to colour code the mismatched results.
You will also find that Excel is also very useful when you need to generate the same sort of statement for hundreds of different column names. Let Excel formulas build your T-SQL for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 4, 2017 at 12:36 pm
I like a third-party tool called Beyond Compare (using the table mode).
Another option is something likeSELECT *
FROM dbo.TEST5_H H
INNER JOIN dbo.TEST5_H2 H2
ON h.PKField1 = h2.PKField
WHERE NOT EXISTS (
SELECT h.*
INTERSECT
SELECT H2.*
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 4, 2017 at 12:50 pm
Try this:
DECLARE @sql nvarchar(max)
SELECT @sql = N'SELECT H.UserID' + CHAR(10)
+ STUFF(( SELECT CHAR(10) + CHAR(9) + ',ISNULL( NULLIF( H.' + QUOTENAME(COLUMN_NAME) + ', H2.' + QUOTENAME(COLUMN_NAME) + '), '''') AS '+ QUOTENAME(COLUMN_NAME) +
+ ',ISNULL( NULLIF( H2.' + QUOTENAME(COLUMN_NAME) + ', H.' + QUOTENAME(COLUMN_NAME) + '), '''')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEST5_H'
AND ORDINAL_POSITION > 1
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
+ N'FROM TEST5_H H JOIN TEST5_H2 H2 ON h.PKFiedl= h2.PKField' + CHAR(10)
+ STUFF(( SELECT 'OR H.' + QUOTENAME(COLUMN_NAME) + '<> H2.' + QUOTENAME(COLUMN_NAME) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEST5_H'
AND ORDINAL_POSITION > 1
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, N'WHERE ')
--PRINT @sql
EXEC( @sql);
May 4, 2017 at 1:38 pm
Use EXCEPT to find the mismatched.
UNPIVOT those rows, and Use EXCEPT again.
May 4, 2017 at 2:07 pm
Using EXCEPT to isolate those handful of rows that are different, copy the results from both tables into WinMerge to do side by side flat text comparison.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 5, 2017 at 2:46 am
You could even try our hosts (Hi Redgate!) own product SQL Data Compare free for 14 days...
Yes, I'm a Friend of Redgate. No, they don't give me cash money to be here or to say these things...
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply