| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2010-03-23 : 11:01:24
|
| I would like to list the fields that are different in duplicate records in two tables...Below is code that will find duplicate records in two tables. What I would LOVE to list is the fields that are different in the duplicate records... in general there will only be one student ID record duplicated in each table.SELECT *from StudentDirectory a left join newstudents b on a.StudentID=b.StudentIDwhere b.StudentID is not nullthank you for the help! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 11:03:46
|
| i cant understand meaning of left join here. you're taking left join and then looking for non nulls which is same as inner join, then why dont you use inner join itself? also, which is the table having duplicates per studentid?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-23 : 11:12:08
|
| i would be fine with inner join as below also... the question to be answered is...for the returning students, what's changed from last semester's directory, to the information they are now providing (the update table)...the second (update) table would contain new students (not in last semester directory ... insert) and old students with new field information (returning from last semester...update)the newstudents table contains about 300 fields... for example lets say the student has a new dorm assignement, I would like to show the difference, between the master table (from last semester) and update table (new semester)...select a.studentIDfrom StudentDirectory a inner join NewStudents b on a.StudentID=b.StudentID order by a.StudentID |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 12:21:15
|
for that isnt this enough?INSERT INTO StudentDirectorySELECT columns...FROM NewStudents nLEFT JOIN StudentDirectory sON s.StudentID=n.StudentIDWHERE s.StudentID IS NULLUPDATE sSET s.field1=n.field1,....FROM NewStudents nINNER JOIN StudentDirectory sON s.StudentID=n.StudentID the above will do update for already existing records in Student main table and for new ones it will do insert------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-23 : 13:44:55
|
| no...not really...I will be appending each record, even if it an update, so that I can report history (basically what changed)...I guess that is what I am asking..how do I report the delta's..what changed, between two records when their key field is the same...to select the most current record, when I do an insert, I add a field 'Entered' that contains today's date.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 13:47:49
|
| same as above, left join gives you new addition and inner join gives you ones in both and you can compare on other fields like n.field1<> s.field1 or n.field2 <> s.field2... to get only those which changed among them. or use a standard audit column like date modified if available in table and look for once with date value > previous max date------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-23 : 15:25:03
|
| no magic...I understand how to capture the entire record, when a field compare fails... but is there a way to report, just on the fields that changed?...right now I have a 120 field record (when using the left join turns that into a 140 field record (120 fields from the master table, and 120 fields in the update table)... do I use some kind of conditional select on n.field1<> s.field1 or n.field2 <> s.field2 ... the objective would be to display only the changed fields within a record...or should I really just use sql to form the table of changed records, and use a report writer to display only the changed fields?...eg. n.dormroom = Altgeld Hall as OldDormRoom, Room 120; s.dormroom = Altgeld Hall, Room 342 as NewDormRoom.... I see from the code above how to form the table of only the 240 fields from the left join (assuming the tables have 120 field per record)... but what I was wanting in my resulting table would be the keyID and the fields that have changed...StudentID OldDormRoom NewDormRoomR130012 Altgeld Hall, Room 120 Altgeld hall, Room 342 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 23:55:16
|
| I repeat again...You can dispense with this field by field checking provided you've an audit column like datemodified. Now if you want to return only fields that changed value you can use a trigger inside which you can use COLUMNS_UPDATED to get changed column info alone (columns that took part in update)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-03-24 : 09:23:46
|
| I do have an audit column, Entered (datetime stamp)...so I am not doing a field by field check...I've not used triggers before...but will give it a go!..and yes, I do want to return only fields that changed value...I will read up on the COLUMNS_UPDATED() function..thank you for the advice! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-24 : 11:26:31
|
| welcome..let us know if you hit a stumbling block------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Find duplicates, list fields that are different