For each column?

Question

For each column?

stricknyn

SSCertifiable

Points: 6560
More actions
January 31, 2008 at 10:35 am

#186244

Hello everyone,
VB 2005 has a For Each which you can use to loop through columns in a datatable/dataset. I'm looking for the equivalent in T-SQL if it can be done. I will be looping through the columns and comparing the fields in a row to another table.
Thanks,
Strick

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

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

Eichpeel Ten Centuries Points: 1079 More actions · Answer 1

If you are trying to compare 2 tables that have the same fields, you could try using a FULL JOIN.

"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You could use the INFORMATION_SCHEMA.Columns view to get to the column names, and then conjure up some dynamic SQL to do what you're looking to do.

But still - if you're planning to do a row-by-row, column by column comparison using loops, you're going to be sitting there a while for it to return. VB.NET does it that way because it doesn't have any other way, but as a set-processor, SQL has a LOT more options, all of which a quite a bit more efficient.

If I may ask - what is it you're looking for? Might help if you were a bit more specific.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

stricknyn SSCertifiable Points: 6560 More actions · Answer 3

Thanks guys,

I was actually looking into the method you suggested Matt.

Also to answer your question, Matt, I'm implementing a trigger which will track all updates from tableA. It will update a history table (ie tableB) with the exact field that was updated from tableA. It will also add the old value and new value in the history table.

Reason I asked about the looping is because when and update occurs, that record is updated in the temporary tables (inserted and deleted) that the trigger creates. While I know that the record was updated, my process needs to know the old and new value and the field that was updated. In order to do this, I needed to compare the fields in the insert and deleted table and insert the non matches in the history table since that's what was updated.

Strick

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

That's where I was thinking you were going. You really don't want to use dynamic SQL for that in my opinion.

I'm also not sure why so many think that the "history table" with each column being individually stored is better than preserving the old table structure. It just seems a lot cleaner, faster and actually IMO a lot less wasteful) to maintain the history table in a way that it matches with the production table instead of one row in the history = one column. It allows you to rolls stuff back a LOT faster, maintains type integrity, etc.... In short - a lot of reasons.

Anyway - assuming ID is your primary key, you simply want to run something like this:

Insert into History (OldID, FieldName, oldValue,NewValue, updatedBy, updatedDate)

select i.id,'myfieldname', i.myfieldname, d.myfieldname,'bob',getdate()

from inserted i INNER JOIN deleted d on i.id=d.id and i.myfieldname<>d.myfieldname

UNION ALL

select i.id,'myfieldname2', i.myfieldname2, d.myfieldname2,'bob',getdate()

from inserted i INNER JOIN deleted d on i.id=d.id and i.myfieldname2<>d.myfieldname2

UNION ALL

etc...

and yes - I would hard code them all. because triggers in my mind just aren't the place to be messing around with dynamic SQL and EXEC, and making sure nothing is wrong (so that the update doesn't get rolled back.

Again though - a history table that matched the original table (with perhaps some tracking as to when and who updated) is IMO a lot LESS taxing than this...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

stricknyn SSCertifiable Points: 6560 More actions · Answer 5

Matt,

We are definately seeing eye to eye on this one.

It's funny that you knew exactly where I was going with this once I explained it. We actually had a friggin "Battle of the IT" because it was split with some wanting to do it this way and others wanting to do it by preserving the record as is. And you can see which one won..lol

In looking at your SQL, it looks like this is exactly what I need. I'm going to test it out.

Thanks for probing for more info. I usually try to keep my questions very generic so people can understand where I'm looking for help, but it seems like you may have experienced this in the past since you knew where I was going with this.

Strick