Setting mask to find out which records changed

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

SQL Server Development (2000)

Setting mask to find out which records changed

Author

Topic

achikha
Starting Member

2 Posts

Posted - 2006-01-31 : 12:01:20

I am trying to set a mask that can go through my rows and find out if any of the element changed that what they were on the previous record and set some sort of mask.

But the problem is that the number of columns are dynamic, (limited to 32 columns is fine)..... but how do I say set the mask to 1011 if the first, third and fourth column changed??

Here's my test script:

DROP TABLE #Results
CREATE TABLE #Results (Indx int, Id int, Name varchar(64), Address varchar(64), Mask int)

INSERT INTO #Results (Indx, Id, Name, Address, Mask)
VALUES (1, 1, 'my name is', '124 street', 0)

INSERT INTO #Results (Indx, Id, Name, Address, Mask)
VALUES (2, 1, 'my name wass', '1234 street town', 0)

select * from #Results R

UPDATE R
SET Mask = 1
from #Results R
inner join #Results R2 on R.Indx = (R2.Indx-1)

achikha
Starting Member

2 Posts

Posted - 2006-01-31 : 12:54:17

Here's what I have come up with so far:
But I don't know if there is a way to loop through columns of a temp table:

DROP TABLE #Results
CREATE TABLE #Results (Indx int, Id int, Name varchar(64), Address varchar(64), Zip int, Mask int)

INSERT INTO #Results (Indx, Id, Name, Address, Zip, Mask)
VALUES (1, 1, 'my name is', '1224 street', 55441, 0)

INSERT INTO #Results (Indx, Id, Name, Address, Zip, Mask)
VALUES (2, 1, 'my name wass', '124 street', 55224, 0)

select * from #Results R

declare @ColumnName varchar(64)
declare @1 int

set @ColumnName = 'Name'
set @1 = 1

--for ()
--begin
--set @i = @1 + 1
exec ('
UPDATE R
SET R.Mask = case when R.' + @ColumnName + ' <> R2.' + @ColumnName + ' then power(2,' + @1 + ') else 0 end | R.Mask
from #Results R
inner join #Results R2 on (R.Indx-1) = R2.Indx
')
--end

select * from #Results R

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-31 : 13:10:46

Check out the UPDATED function in Books Online. I believe it implements a mask much like you are trying to recreate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources