Logic for traversing thru different columns in one row

Question

Post reply

Logic for traversing thru different columns in one row

sql_learner29

SSCrazy Eights

Points: 9107
More actions
October 29, 2008 at 9:49 am

#200614

Hi all,
I have a requirement to loop through a row for different columns, Can anyone give me some inputs for how can I do this in SSIS.
The question is like this:
I am tracking changes to certain columns, now I want to keep record in a fact table ,for changed columns in one row ...
for ex:
If I have these columns from my source:-
empno ename address city state phone
now the columns which are critical and i need to maintain changes are only address, city , state
so for any change in address create a record in fact with changes done ..
but the question here is suppose if 2 critical attributes changes at the same time and come to me together...now it will create record for first one that it looked up and will not lookup again to the same row for other changed column...but i want to loop and lookup again for other critical attributes that may have changed...
Actually, I am not getting any logic for this Can any one provide me sum help on this?
Thanks in Advance
Ruchika
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 1

any help plzzzzzz

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

you could easily do this automatically in a trigger, especially by using the new COLUMNS_UPDATED() function that exists for triggers in SQL2005; if a change occurs, you could have a trigger automatically log the changes to an audit table...

Are you sure you need to do this in SSIS, or just 'get er done'?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

Thanks......I don't know how trigger will help me to track every column...

will it insert a new row for every column changed in a row?

I mena if address and location both changes, will it give me two rows in the transaction table?

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok here's an example for you to play with to understand the concept:

create the two tables, and select from the two tables after every step;

this example says if columns 3,4,5,6,7 or 8 are modified, logit to an audit table; changing column 1,2 or 9 does not cause an audit.

updating a value to the same value still triggers an audit..so if you set ADDR1 = to the same value it had previously, it still fires the trigger.

[font="Courier New"]

CREATE TABLE MYADDRESSES(

EMPNO INT IDENTITY(1,1) PRIMARY KEY,

ENAME VARCHAR(100),

ADDR1 VARCHAR(100),

ADDR2 VARCHAR(100),

CITY VARCHAR(100),

STATECODE VARCHAR(2),

ZIPCODE VARCHAR(100),

PHONE VARCHAR(20),

MOREDATA VARCHAR(100)) --column 9 will not track changes

CREATE TABLE ADDRESSCHANGES(

EMPNO INT ,

COLCHANGEDBITMASK INT, --example: saved so you can audit which cols actually changed, but they are both here anyway!

OLDENAME VARCHAR(100),

OLDADDR1 VARCHAR(100),

OLDADDR2 VARCHAR(100),

OLDCITY VARCHAR(100),

OLDSTATECODE VARCHAR(2),

OLDZIPCODE VARCHAR(100),

OLDPHONE VARCHAR(20),

NEWENAME VARCHAR(100),

NEWADDR1 VARCHAR(100),

NEWADDR2 VARCHAR(100),

NEWCITY VARCHAR(100),

NEWSTATECODE VARCHAR(2),

NEWZIPCODE VARCHAR(100),

NEWPHONE VARCHAR(20))

GO

--modified from http://www.sqlservercentral.com/Forums/Topic593727-148-1.aspx#bm595054

CREATE TRIGGER TR_MYADDRESSES

ON MYADDRESSES

AFTER UPDATE AS

/*Check whether columns 3 thru 8 have been updated. If any or all

columns 2, 3 or 4 have been changed, create an audit record. The

bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test

whether all columns 2, 3, and 4 are updated, use = 14 instead of > 0

(below).

this one specifically is for 32 thru 8 is:

select power(2,(3-1))

+ power(2,(4-1))

+ power(2,(5-1))

+ power(2,(6-1))

+ power(2,(7-1))

+ power(2,(8-1)) = 252*/

--IF (COLUMNS_UPDATED()) <> 0

IF (SUBSTRING(COLUMNS_UPDATED(),1,1) & 252 )>0

BEGIN

--inside a trigger, two special tables exist for the duration of the trigger:

--the table INSERTED and the table DELETED

--an UPDATE would have data in both tables...the value WAS DELETED to be replaced with the value from INSERTED

INSERT INTO ADDRESSCHANGES(EMPNO, COLCHANGEDBITMASK, OLDENAME, OLDADDR1, OLDADDR2,

OLDCITY, OLDSTATECODE, OLDZIPCODE, OLDPHONE,

NEWENAME, NEWADDR1, NEWADDR2,

NEWCITY, NEWSTATECODE, NEWZIPCODE, NEWPHONE)

SELECT

DELETED.EMPNO,

COLUMNS_UPDATED(),

DELETED.ENAME,

DELETED.ADDR1,

DELETED.ADDR2,

DELETED.CITY,

DELETED.STATECODE,

DELETED.ZIPCODE,

DELETED.PHONE,

INSERTED.ENAME,

INSERTED.ADDR1,

INSERTED.ADDR2,

INSERTED.CITY,

INSERTED.STATECODE,

INSERTED.ZIPCODE,

INSERTED.PHONE

FROM DELETED

INNER JOIN INSERTED

ON DELETED.EMPNO = DELETED.EMPNO

END;

GO

--insert some test data

INSERT INTO MYADDRESSES( ENAME, ADDR1, ADDR2, CITY, STATECODE, ZIPCODE, PHONE, MOREDATA)

SELECT 'Kalvin','123 My Imagination St','','Miami','FL','33024','555-1212','likes snowmen'

UNION

SELECT 'Hobbes','123 My Imagination St','','Miami','FL','33024','555-1222','likes to tease calvin'

--renaming Calvin doesn't raise the trigger

UPDATE MYADDRESSES SET ENAME='Calvin' WHERE ENAME='Kalvin'

--changing the Address DOES:

UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' WHERE ENAME='Calvin'

--changing multiple rows at the same time does trigger

UPDATE MYADDRESSES SET STATECODE='NY'

--setting the value to the same value repeating a previous insert:

UPDATE MYADDRESSES SET ADDR1='123 G.R.O.S.S. Clubhouse' WHERE ENAME='Calvin'

SELECT * FROM MYADDRESSES

SELECT * FROM ADDRESSCHANGES[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks man for the prompt response...let me try dis...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 6

Triggers been expensive...Can anyone tell me any other way to do this..I got from some forum that we can use CHECKSUM function or CHECKSUM transformation for this...

Have anyone got any idea abt how to use the checksum TSQL or transformation?

I never used it so have no idea at all....

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]