Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Compare two tables: Which fields are different? Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2008 8:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:53 AM
Points: 55, Visits: 276
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

Post #511440
Posted Wednesday, June 4, 2008 9:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:53 AM
Points: 55, Visits: 276
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

Post #511516
Posted Friday, June 6, 2008 7:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
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
Post #512856
Posted Saturday, June 7, 2008 7:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:53 AM
Points: 55, Visits: 276
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).
Post #513395
Posted Tuesday, June 15, 2010 3:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 8:40 AM
Points: 1, 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?


Post #937844
Posted Tuesday, June 15, 2010 3:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:53 AM
Points: 55, Visits: 276
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.
Post #937855
Posted Tuesday, June 15, 2010 3:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
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
Post #937868
Posted Tuesday, June 15, 2010 8:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:53 AM
Points: 55, Visits: 276
Kudos to whomever allowed me to subscribe to a topic and get an email.
Post #937903
Posted Wednesday, June 30, 2010 4:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 6, 2010 5:37 AM
Points: 11, 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.
Post #945352
Posted Wednesday, June 30, 2010 9:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:01 PM
Points: 3,843, Visits: 3,836
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
Post #945588
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse