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.
| Author |
Topic |
|
Xerxes
Aged Yak Warrior
666 Posts |
 Posted - 2005-01-13 : 16:07:02
|
| I have a 100K record file that has 60 columns. 35 of the columns read as follows:COL1Name, COL1Start, COL1End, COL1Priority, COL1Check,COL2Name, COL2Start, COL2End, COL2Priority, COL2Check,COL3Name, COL3Start, COL3End, COL3Priority, COL3Check,COL4Name, COL4Start, COL4End, COL4Priority, COL4Check,COL5Name, COL5Start, COL5End, COL5Priority, COL5Check,COL6Name, COL6Start, COL6End, COL6Priority, COL6Check,COL7Name, COL7Start, COL7End, COL7Priority, COL7CheckI wish to perform the various edits on each in every record. This would be easier if I could loop (without cursors) through the column names (in a manner similar to what can be done in other languages like SAS and C+). Here is my attempt:SELECT @ColumnSeq = @ColumnSeq + 1 WHILE @ColumnSeq <= 7 BEGIN SELECT @ColumnSeqChar = CAST(@ColumnSeq as varchar(1)) SELECT @COLName = RTRIM('COL'+@ColumnSeqChar+'Name') SELECT @COLPriority = RTRIM('COL'+@ColumnSeqChar+'Priority') SELECT @COLStart = RTRIM('COL'+@ColumnSeqChar+'Start') SELECT @COLEnd = RTRIM('COL'+@ColumnSeqChar+'End') SELECT @COLCheck = RTRIM('COL'+@ColumnSeqChar+'Check') SELECT @COLName, @COLPriority, @COLStart, @COLEnd, @COLCheck = CASE when UPPER(@COLPriority) = 'P' then 'OK' END END...but there's no loop control and no way to UPDATE the record as I go.Anyone got any ideas on how I can proceed? Thanks..~~~~~~~~~~~~~Semper fi, Xerxes, USMC |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 17:59:36
|
If you want to do this in an sp you can read the column names from the Information_Schema.columns Do you want to process 1 column for every row or every column for one row.You can use [BLUE]Insert into #TempMyKey Table Select MyPrimaryKeyColumns from MyTableDeclare @MyPriamryKey as intDo While Exists(Select * from TempMyKey)BEGIN Select Top 1 @MyPriamryKey = MyPriamryKey from #TempMyKey Delete #TempMyKey Where MyPriamryKey = @MyPriamryKey Process some stuffEND[\BLUE]Just add additional loops |
 |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 17:59:57
|
| If you want to do this in an sp you can read the column names from the Information_Schema.columns Do you want to process 1 column for every row or every column for one row.You can use [BLUE]Insert into #TempMyKey Table Select MyPrimaryKeyColumns from MyTableDeclare @MyPriamryKey as intDo While Exists(Select * from TempMyKey)BEGIN Select Top 1 @MyPriamryKey = MyPriamryKey from #TempMyKey Delete #TempMyKey Where MyPriamryKey = @MyPriamryKey Process some stuffEND[\BLUE]Just add additional loops |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-13 : 22:17:09
|
| Please, please consider normalizing your database then you won't have to deal with stuff like this.- Jeff |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-01-27 : 14:04:29
|
quote:
Originally posted by clarkbaker1964
If you want to do this in an sp you can read the column names from the Information_Schema.columns Do you want to process 1 column for every row or every column for one row.You can use [BLUE]Insert into #TempMyKey Table Select MyPrimaryKeyColumns from MyTableDeclare @MyPriamryKey as intDo While Exists(Select * from TempMyKey)BEGIN Select Top 1 @MyPriamryKey = MyPriamryKey from #TempMyKey Delete #TempMyKey Where MyPriamryKey = @MyPriamryKey Process some stuffEND[\BLUE]Just add additional loops
Just wanted to thank you for your helpful response!~~~~~~~~~~~~~Semper fi, Xerxes, USMC |
|
|
|
|
|
|
|
|
Edit/Update Sequentially-named Columns Dynamically