March 28, 2008 at 2:42 pm
need to write a cursor to compare two tables(original and updated tables)
1.display changes to field values in existing records
2.new records added
3.existing records deleted
March 28, 2008 at 2:59 pm
Do the tables have the same unique key that you could join on? If they do you do not need a cursor. You could use a full outer join and case statements. Something like:
Declare @changes Table (change_type varchar(25), [field list])
Insert Into @changes
Select -- get new records
'New Records',
O.*
From
original_table O Left Join
update_table U On
O.unique_key_fields = U.unique_key_fields
Where
U.key_fields is null
UNion
Select -- get deleted records
'Deleted Records',
O.*
From
original_table O Right Join
update_table U On
O.unique_key_fields = U.unique_key_fields
Where
O.key_fields is null
-- get modified records
Insert Into @changes
Select
'modified records',
Case
When O.field1 <> U.field1 then O.field1 + '|' + U.field1 -- concatentating the fields to display different values
Else 'Not Changed'
End as field1 -- add as many fields as needed
From
original_table O Join
update_table U On
O.unique_key_fields = U.unique_key_fields
Where
O.field_list_concatenated <> U.field_list_concatenated
Select * from @changes
This should perform better than a cursor. Not sure exactly what your requirements are for output.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2008 at 11:03 am
Thank u for ur response.........i will try this out............
March 30, 2008 at 11:07 am
they asked to use curser becoz it needs to check the records every time for the changes for every baselineobject...........
March 30, 2008 at 12:59 pm
chinni (3/30/2008)
they asked to use curser becoz it needs to check the records every time for the changes for every baselineobject...........
Doesn't Jack's script do that?
If not, then you'll have to explain things a little better. Check this link out for tips on how to get better answers to your questions on the forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2008 at 5:26 pm
chinni (3/30/2008)
they asked to use curser becoz it needs to check the records every time for the changes for every baselineobject...........
I don't understand... why do you think that requires a Cursor?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 3:17 am
Maybe because he/she means the broader meaning of cursor.
Every select creates a cursor.
Comparing two tables is IMHO a bad solution, because over time there will be a lot of differences, so a compare would take a lot of time. Even if there are not a lot of changes, compare takes some time, if table is huge. If you synchronize the tables, you lose the history. Also, it gets a lot more complicated, when the table has references, which usually does.
A good solution would be logging changes into a history table with triggers.
1. triggers decide what and how to log changes
2. you can have human readable change log
3. you can selectively purge parts of history that become obsolete.
March 31, 2008 at 5:51 am
Robert (3/31/2008)
Maybe because he/she means the broader meaning of cursor.
Considering the level of the question, I wouldn't think so. I sure wish the OPs would answer these questions so we actually know instead of guessing. 😉 Thinking that a cursor is needed means that, in most cases, something is missing from the problem description.
Also, a merge of this type doesn't necessarily cause a loss of "history". It depends on how it is written. But I don't know how it needs to be written because you keep trying to answer questions for the OP 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 6:10 am
What does "OP" stand for? Ops, original poster :blush:
Considering the level of the question, I wouldn't think so either. However, when dealing with customers or assisting fellows developers on their learning path, you suspect, but never assume things based on missing information. 😉
March 31, 2008 at 8:02 am
i have a bunch of tables to be compared.........all the table names are in one virtual table.............
thanks for ur response
March 31, 2008 at 10:11 am
if we use left join we display all records in left table.......but we need to display only new record.........
March 31, 2008 at 10:19 am
chinni (3/31/2008)
if we use left join we display all records in left table.......but we need to display only new record.........
That is where the Where U.key_fields is null comes in, U.key_fields represents the data in the Right Table. When it is NULL there is not record, thus it will show only the new records.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2008 at 7:06 pm
Robert (3/31/2008)
What does "OP" stand for? Ops, original poster :blush:Considering the level of the question, I wouldn't think so either. However, when dealing with customers or assisting fellows developers on their learning path, you suspect, but never assume things based on missing information. 😉
Yes... it stands for "Original Poster".
So far as the other, that's why I didn't want you to answer the question... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply