Getting Changed Values. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Getting Changed Values.

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 04:23:01

Hi All,

I have to 2 tables, the architecture wise they are all most similar. Here the Table structure

Table TblMain

EmpID Int
Loclib1 Varchar(9)
Loclib2 Varchar(9)
Loclib3 Varchar(9)
Loclib4 Varchar(9)
Loclib5 Varchar(9)
Basic currency
HRA currency
HRAType smallint
Aux1 currency
Aux1Type smallint
Aux2 currency
Aux2Type Smallint

Table TblTemp

CanID Int
Loclib1 Varchar(9)
Loclib2 Varchar(9)
Loclib3 Varchar(9)
Loclib4 Varchar(9)
Loclib5 Varchar(9)
Basic currency
HRA currency
HRAType smallint
Aux1 currency
Aux1Type smallint
Aux2 currency
Aux2Type Smallint

Now here is what i want


TblMain Data 
EmpID  Loclib1  Loclib2  Loclib3  Loclib4  Loclib5  Basic HRA  HRAType                  
1      Loc1      Loc2     Loc3    Loc4     Loc5     1200  100   1

TblTmp Data 
CanId  Loclib1  Loclib2  Loclib3  Loclib4  Loclib5  Basic HRA  HRAType                  
1      Loc9      Loc10    Loc11   Loc12   Loc13     1100  100   1

Now i want the list of all the elements which are changed somthing like this 

EmpID [Element Changed]  From    To 
1       Loclib1          Loc1   Loc9 
1       Loclib2          Loc2   Loc10 
1       Loclib3          Loc3   Loc11 
1       Loclib4          Loc4   Loc12
1       Loclib5          Loc5   Loc13 
1       Basic            1200   1100 


CanId and EmpId are linked to gather

Is there any way to do so using SQL ?? else i need to do in front end using oldvalue and newvalue and comparing them if its different then insert into same table

Thanks

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 04:33:41

The only way i can think of is using UNION ALL. I only did it for the 1st 2 column. You can expand to the rest.


select	m.EmpID, 
	case when m.Loclib1 <> t.Loclib1 then 'Loclib1' else null end as [Element Changed],
	case when m.Loclib1 <> t.Loclib1 then m.Loclib1 else null end as [From],
	case when m.Loclib1 <> t.Loclib1 then t.Loclib1 else null end as [To]
from	TblMain m inner join TblTemp t
	on	m.EmpID	= t.CanID
where	m.Loclib1 <> t.Loclib1

union all

select	m.EmpID, 
	case when m.Loclib2 <> t.Loclib2 then 'Loclib2' else null end as [Element Changed],
	case when m.Loclib2 <> t.Loclib2 then m.Loclib2 else null end as [From],
	case when m.Loclib2 <> t.Loclib2 then t.Loclib2 else null end as [To]
from	TblMain m inner join TblTemp t
	on	m.EmpID	= t.CanID
where	m.Loclib2 <> t.Loclib2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 04:43:37

Actually you can remove the case when.... end as the checking has been done in the WHERE clause


select	m.EmpID, 
	case when m.Loclib1 <> t.Loclib1 then 'Loclib1' else null end as [Element Changed],
	case when m.Loclib1 <> t.Loclib1 then m.Loclib1 else null end as [From],
	case when m.Loclib1 <> t.Loclib1 then t.Loclib1 else null end as [To]
from	TblMain m inner join TblTemp t
	on	m.EmpID	= t.CanID
where	m.Loclib1 <> t.Loclib1

union all

select	m.EmpID, 
	case when m.Loclib2 <> t.Loclib2 then 'Loclib2' else null end as [Element Changed],
	case when m.Loclib2 <> t.Loclib2 then m.Loclib2 else null end as [From],
	case when m.Loclib2 <> t.Loclib2 then t.Loclib2 else null end as [To]
from	TblMain m inner join TblTemp t
	on	m.EmpID	= t.CanID
where	m.Loclib2 <> t.Loclib2

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 04:43:41

Thanks for the Reply..

Actually there are 43 Columns which i need to check for, so what do you suggest should i do it in a front end using NewValue or oldvalue or this method?

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 04:48:08

If it is almost all of the columns of a table, you can consider using dynamic SQL

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 04:53:09

Dynamic SQL ? u mean looping through columns and comparing..

not all but around 80% of the columns of the tables are need to be compared

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-11 : 04:55:40

Yup. Looping throught all required columns and compare.

Or if you don't mind coding the 43 columns name then you can avoid Dynamic SQL.

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 04:57:11

Not quite what you asked for, but for "rough and ready" checking we output the two columns side-by-side, and then an '*' in between if they are different. Just we can scan along a row looking for '*' and then check the adjacent columns:

Note that "where m.Loclib2 <> t.Loclib2" won't catch one being NULL and the other not (unless you mess with the ANSI_NULL settings).


select	m.EmpID, 
	m.Loclib1, CASE WHEN m.Loclib1 = t.Loclib1 THEN '' ELSE '*' END, t.Loclib1,
	m.Loclib2, CASE WHEN m.Loclib2 = t.Loclib2 THEN '' ELSE '*' END, t.Loclib2,
        ...
from	TblMain m inner join TblTemp t
	on	m.EmpID	= t.CanID
where	   (m.Loclib1 <> t.Loclib1 COLLATE Latin1_General_BIN
            OR (m.Loclib1 IS NULL AND t.Loclib1 IS NOT NULL)
            OR (m.Loclib1 IS NOT NULL AND t.Loclib1 IS NULL))
        OR (m.Loclib2 <> t.Loclib2  COLLATE Latin1_General_BIN
            OR (m.Loclib2 IS NULL AND t.Loclib2 IS NOT NULL)
            OR (m.Loclib2 IS NOT NULL AND t.Loclib2 IS NULL))
             ....
        )

NOTE: Use COLLATE Latin1_General_BIN on "string" columns to check for case-sensitive differences

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 05:01:50

I guess i will use this coding for 43 columns, since i can pass the element Name as what i want, in dyamic i dont think it will be possible..
for instance


EmpID  NewElement  From  To 
1       Company    Loc1  Loc9

Insted of Loclib1..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 05:47:54

Hi all,

Just trying to combine what's been said already...

--data
declare @TblMain Table (EmpID Int, Loclib1 Varchar(9), Loclib2 Varchar(9), Loclib3 Varchar(9),
    Loclib4 Varchar(9), Loclib5 Varchar(9), Basic money, HRA money, HRAType smallint)
insert @TblMain values (1, 'Loc1', 'Loc2', null, 'Loc4', null, 1200, 100, 1)

declare @TblTemp Table (CanID Int, Loclib1 Varchar(9), Loclib2 Varchar(9), Loclib3 Varchar(9),
    Loclib4 Varchar(9), Loclib5 Varchar(9), Basic money, HRA money, HRAType smallint)
insert @TblTemp values (1, 'LOC1', 'Loc2', 'Loc3', null, null, 1100, 100, 1)

--calculation
select [Element Changed], [From], [To] from (
    select null as [Element Changed], null as [From], null as [To] from @TblMain where 1 = 0 union all
    select 'Loclib1', a.Loclib1, b.Loclib1 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib2', a.Loclib2, b.Loclib2 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib3', a.Loclib3, b.Loclib3 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib4', a.Loclib4, b.Loclib4 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib5', a.Loclib5, b.Loclib5 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Basic', cast(a.Basic as varchar(10)), cast(b.Basic as varchar(10)) from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'HRA', cast(a.HRA as varchar(10)), cast(b.HRA as varchar(10)) from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'HRAType', cast(a.HRAType as varchar(10)), cast(b.HRAType as varchar(10)) from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID) Transposed
where [From] != [To] COLLATE Latin1_General_BIN
    or ([From] is null and [To] is not null)
    or ([From] is not null and [To] is null)

The transposition is a bit messy because you need to do casts to the same data type.... That said, I had a thought while I was posting this (maybe you can guess it

) and will post an alternative in a sec....

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 05:50:20

Okay - the thought was to use sql_variant. But then that didn't work with collate, so I thought we could compare the binary checksum instead. Something like this...

--data
declare @TblMain Table (EmpID Int, Loclib1 Varchar(9), Loclib2 Varchar(9), Loclib3 Varchar(9),
    Loclib4 Varchar(9), Loclib5 Varchar(9), Basic money, HRA money, HRAType smallint)
insert @TblMain values (1, 'Loc1', 'Loc2', null, 'Loc4', null, 1200, 100, 1)

declare @TblTemp Table (CanID Int, Loclib1 Varchar(9), Loclib2 Varchar(9), Loclib3 Varchar(9),
    Loclib4 Varchar(9), Loclib5 Varchar(9), Basic money, HRA money, HRAType smallint)
insert @TblTemp values (1, 'LOC1', 'Loc2', 'Loc3', null, null, 1100, 100, 1)

--calculation
select [Element Changed], [From], [To] from (
    select null as [Element Changed], cast(null as sql_variant) as [From], cast(null as sql_variant) as [To] from @TblMain where 1 = 0 union all
    select 'Loclib1', a.Loclib1, b.Loclib1 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib2', a.Loclib2, b.Loclib2 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib3', a.Loclib3, b.Loclib3 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib4', a.Loclib4, b.Loclib4 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Loclib5', a.Loclib5, b.Loclib5 from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'Basic', a.Basic, b.Basic from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'HRA', a.HRA, b.HRA from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID union all
    select 'HRAType', a.HRAType, b.HRAType from @TblMain a inner join @TblTemp b on a.EmpID = b.CanID) Transposed
where not binary_checksum([From]) = binary_checksum([To])

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 06:18:12

You need a WHERE to only select ones which are different between TblMain and TblTemp don't you?

Kristen

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 06:32:12

Hi Kristen

There is a where (am I missing something?

)?

Did you try running them?

If you're asking if a where is needed for each select in the union, I don't think they are needed, but maybe would make things more efficient (performance-wise). I thought I'd just do a transposition first, and then do the comparison on that transposed table - for aesthetic reasons

.

It 'looked' a lot neater with the first effort, but now with the checksum idea it wouldn't make so much difference aesthetically.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 06:42:37

"If you're asking if a where is needed for each select in the union"

Yeah, that's what I meant! Assuming the stuff is 99% the same then you will be selecting a lot on the first pass to then throw away with the where not binary_checksum([From]) = binary_checksum([To])

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 07:02:51

Aha Great Replies..

After getting all the methods, what you guys suggest which is the best method to do so?? i m confused..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 07:26:45

quote:
Assuming the stuff is 99% the same then you will be selecting a lot on the first pass to then throw away with the where not binary_checksum([From]) = binary_checksum([To])

Yeah, I know. I just did it that way for aesthetics. I am a bad person.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources