SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare two tables: Which fields are different?


Compare two tables: Which fields are different?

Author
Message
mikeg13
mikeg13
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 317
Ok, I could not let the lack of old and new values wait any longer. Here is the revised code that accounts for whether the old and new values should be included in the results. The @p_details parameter is passed to the procedure and if it is 'YES' then the old and new values are included. If not YES, then it just gives the name of the field that differs. Note that the values are enclosed in [], delimited by ^^ (my original -> gave me problems due to 'xml path' I think). Let me just say that creating dynamic SQL is a PITA.


IF UPPER(@p_details) = 'YES'
SELECT @Changes =
stuff( ( select '+ case when isnull(convert(varchar,A.' + c1.column_name +
'),'''') = isnull(convert(varchar,B.' + c1.column_name +
'),'''') then ''~'' else ''' + c1.column_name +
'=[''+isnull(convert(varchar,A.' + c1.column_name +
'),'''') + ''^^'' +isnull(convert(varchar,B.' + c1.column_name +
'),'''') + '']~'' end '
from information_schema.columns c1
where c1.column_name not in ('LOAD_DTTM','LASTUPDDTTM','DW_LOAD_DTTM','UW_LOAD_DTTM','AGE')
and c1.table_schema = c2.table_schema
and c1.table_name = c2.table_name for xml path('') )
, 1,1,'')
FROM information_schema.columns c2
WHERE table_schema = @Schema1 and table_name = @Table1
GROUP BY c2.table_schema, table_name



ELSE
SELECT @Changes =
stuff( ( select '+ case when isnull(convert(varchar,A.' + c1.column_name +
'),'''') = isnull(convert(varchar,B.' + c1.column_name +
'),'''') then ''~'' else ''' + c1.column_name + '~'' end '
from information_schema.columns c1
where c1.column_name not in ('LOAD_DTTM','LASTUPDDTTM','DW_LOAD_DTTM','UW_LOAD_DTTM','AGE')
and c1.table_schema = c2.table_schema
and c1.table_name = c2.table_name for xml path('') )
, 1,1,'')
FROM information_schema.columns c2
WHERE table_schema = @Schema1 and table_name = @Table1
GROUP BY c2.table_schema, table_name


mikeg13
mikeg13
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 317
Just for completeness, here is the revised code for parsing out the old ane new values into the final table...


IF UPPER(@p_details) = 'YES'
INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm)
SELECT Table_name, Action, Key_fields, Key_values,
SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('=','~'+ Changes +'~',N+1)-N-1) AS Fieldname,
SUBSTRING(Changes,CHARINDEX('[',Changes,N+1)+1,( CHARINDEX('^^',Changes,N+1)-CHARINDEX('[',Changes,N+1)-1) ) AS Old_Value,
SUBSTRING(Changes,CHARINDEX('^^',Changes,N+1)+2,( CHARINDEX(']',Changes,N+1)-CHARINDEX('^^',Changes,N+1)-2) ) AS New_Value,
getdate()
FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b
WHERE a.N < LEN('~'+ Changes +'~')
AND SUBSTRING('~'+ Changes +'~',N,1) = '~'
AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~'
AND table_name = @Table1
ELSE
INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm)
SELECT Table_name, Action, Key_fields, Key_values,
SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('~','~'+ Changes +'~',N+1)-N-1) AS Fieldname,
null AS Old_Value,
null AS New_Value,
getdate()
FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b
WHERE a.N < LEN('~'+ Changes +'~')
AND SUBSTRING('~'+ Changes +'~',N,1) = '~'
AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~'
AND table_name = @Table1


Seggerman-675349
Seggerman-675349
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 559
I was given a similar task a few months ago and my solution was along the lines of the suggestion of glen.greaves -

identify the records with mismatches by using
select table a
union all select table b
except
select table b
union all
select table a

I had a function which took two fields and the name of the field and returned a value if the fields didn't match
field by field of the records that didn't match I looked for the field(s) with mismatches

oh and the first step was to limit the rows in table a and table b to records that matched on key fields
mikeg13
mikeg13
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 317
The UNION ALL and EXCEPT method will work and is easier to understand, but it is too time consuming for me. If you use 'select *' with the EXCEPT, you will get the different records, but have no idea what columns changed. However, you could loop through the syscolumns table and create dynamic sql to use this method for each column. Like I said, this is easier to understand and certainly easier and quicker to code. Using this method requires reading the tables once for every column in the table. My method checks everything in one single pass. With some tables with hundreds of columns, I prefer my method (but am always looking for a quicker and easier method).
viet074
viet074
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
Hi mikeg13. I was going through your sample as I need to implement something similar. I was wondering if you could answer a couple of questions.

1. I could not locate anywhere in the code sample where you actually inserted into the staging table. Did I miss something?
2. Where does the Admin.Tally table get created and populated?
mikeg13
mikeg13
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 317
It looks like I forgot to add the dynamic SQL that puts all these pieces together.


-- Piece together everything into a single dynamic INSERT statement
SET @SQL = 'INSERT INTO Stage.DATA_CHANGES (Tablename, Action, Key_Fields, Key_Values, Changes) SELECT * FROM (SELECT ''' +
@p_changes_tablename + ''' tname, ''UPDATE'' action, ''' + @Key_Fields + ''' kfields, ' + @Key_Values + ' kvalues, ' +
@Changes + ' Changes ' + @From + ' WHERE ' + @Join_Keys +
') alias1 WHERE len(replace(changes,''~'','''')) > 0'


-- Run the dynamic SQL to populate the staging table
EXECUTE (@SQL )



As for the "Tally" table, that is just a table of numbers. The idea for this can be found by searching this forum for "tally table" or try [url=http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/][/url]

Hopefully this helps. I would be interested to know if you, or anyone else, gets any use out of this. Good luck.
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6270 Visits: 4551
Kudos on sticking with a thread that is 2 years old!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
mikeg13
mikeg13
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 317
Kudos to whomever allowed me to subscribe to a topic and get an email. :-)
mohammad.kamranpk
mohammad.kamranpk
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 22
I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).

For Example I have two tables Table_A and Table_B as below:

Table_A
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190


Table_B
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190

and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:

Table_C
----------------------
Sim_RecordID Sim_StudentID Sim_Dept SimBookID
1 1 1 1
1 0 0 1
1 1 1 1

Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6270 Visits: 4551
Mohammed,

You posted your question on a thread that is 2 years old. I suggest that you open a new thread with your question.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search