Loop through each record and then each field within each record

Question

Loop through each record and then each field within each record

dba321

Hall of Fame

Points: 3917
More actions
December 15, 2005 at 8:55 pm

#364431

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor
AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @FieldName, @AcctNumber, @Screen, @CaseNumber, @BKYChapter, @FileDate, @DispositionCode, @BKUDA1, @RMSADD2, @RMSCHPNAME_1, @RMSADDR_1,
@RMSCITY_1, @RMSSTATECD_1, @RMSZIPCODE_1, @RMSWORKKPHN, @BKYMEETDTE, @RMSCMPNAME_2, @RMSADDR1_2, @RMSCITY_2, @RMSSTATECD_2,
@RMSZIPCODE_2, @RMSHOMEPHN, @BARDATE, @RMSCMPNAME_3, @RMSADD1_2, @RMSADD2_3, @RMSCITY_3, @RMSZIPCODE_3, @RMSWORKPHN_2
WHILE @@FETCH_STATUS = 0
BEGIN
@Chapter = chapter for this record
For each column in current record <---- not sure how to code this part is what I'm referring to
            do some stuff here using sql for the column I'm on for this row
     Next
Case @Chapter
Case 7

    Insert RecoverCodeRecord
      Insert Status Code Record
    Insert Attorney Code Record
Case 13
    Insert Record
      Insert Record
    Insert Record
Case 11
    Insert Record
      Insert Record
    Insert Record
Case 12
    Insert Record
      Insert Record
    Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Viewing 15 posts - 1 through 15 (of 21 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Maybe I'm looking at this at the end of a long day so don't really understand what you want to do...I could give you some "concepts" to mull on and if I'm too far off the mark, just let me know...

1) I don't know what " @Chapter = chapter for this record" means! Is it one of the columns in your table - if so, I don't see it in your "fetch..into" statement!!!

2) One thing you could do is to wrap your "do some stuff here using sql for the column I'm on for this row" into another (either one or many depending on what you need to do) stored procedure and then execute that procedure passing your @Chapter as a parameter to it.

3) Like so...

WHILE @@FETCH_STATUS = 0
   @Chapter = chapter for this record
   
   BEGIN
      EXEC procInsertRecords @Chapter
   END
   FETCH NEXT FROM.....

4) Your "procInsertRecords" of course, would do all the "InsertRecord" stuff based on the @Chapter value - could be just a simple...

IF @Chapter = 7
   BEGIN
      ...do stuff
   END
ELSE IF @Chapter = 8
   BEGIN
      ...do stuff
   END
ELSE 
   BEGIN
      ...do stuff
   END

It'd be great if you could provide more details of what you want to do - before someone can suggest alternative solutions!

**ASCII stupid question, get a stupid ANSI !!!**

dba321 Hall of Fame Points: 3917 More actions · Answer 2

forget everything else. all I am asking here is how to loop through each field in the row in my cursor for that row...move to the next row in my cursor, loop through each field of that row, and so on because I need to perform a statement for each field in each row individually. I just pasted my whole code, just concentrate on the part where I want to loop throught the columns and do some sql on each column for the current row in the cursor. As you can see the cursor is iterated row by row. I need to perform stuff on each field for the row then move tothe next row and repeat.

so to sum, just tell me how to do a loop and perform an operation on each field in the cursor's row

dba321 Hall of Fame Points: 3917 More actions · Answer 3

I don't necessarily need an alternative solution here. Just as a cursor can move through each row, what about each column in a row! ??

philcart SSC-Forever Points: 47794 More actions · Answer 4

Personally, I wouldn't be "forgetting" everything else.

You're basically running a cursor within a cursor, and I'm sure I'm not the only one that would point out the possible performance issues with this approach.

Maybe if you qualify what "... I need to perform stuff on each field ..." means, a better solution might be forthcoming.

--------------------
Colt 45 - the original point and click interface

dba321 Hall of Fame Points: 3917 More actions · Answer 5

for example:

WHILE @@FETCH_STATUS = 0

BEGIN

...

For each column in current cursor row <---- not sure how to code this part is what I'm referring to

@columnvalue = the value of the column we're on

@columname = the name of the column we're on in this loop

Insert somevalue1, somevalue2, @columnvalue, somevalue3....INTO....

END

dba321 Hall of Fame Points: 3917 More actions · Answer 6

What I have to do for this project is take a bunch of records. For each record, I have to insert a new record for each field of that record in another table.

I am dealing with a stupid ERP system that only takes updates to records by individual fields.

so for example if I have a record like this:

AcctID zip phone

I am creating a program to insert a record into another table like this:

AccountID Zip

AccountID Phone

Then move on to the next record in my result set and do the same

So what I thought I could do is put all the records in a cursor first, iterate through the cursor but then also iterate through each field so that I can insert a record into a new table for each damn field for each row....it is a requirement, don't ask me why the stupid ERP system's UI can only process updates like this but it does. So in the end, my program will create a flat file that looks like this:

AccountID Zip

AccountID Phone

AccountID nextfield

it's retarded which is probably why nobody gets this.

dba321 Hall of Fame Points: 3917 More actions · Answer 7

extending on above. So again lets' say I have these records in my result set

AcctID Zip Phone

11212 23332 333-444-5555

23223 34434 444-333-5545

12123 64554 554-344-3334

I need to create a flatfile in the end. So first I'll need this in my end result table:

11212 23332

11212 333-444-5555

23223 34434

23223 444-333-5545

12123 64554

12123 554-344-3334

then I'll just save this table as a flat file, 100 byte file. I will also append some additional values after each row in the final table..some static values that each row needs in order for the stupid ERP system to use each row in the flat file to update the fields.

philcart SSC-Forever Points: 47794 More actions · Answer 8

Without a loop ...

set nocount on

Create table #tmp (
 AcctID int
 , Zip varchar(10)
 , Phone varchar(20)
)

insert into #tmp values (11212, 23332, '333-444-5555')
insert into #tmp values (23223, 34434, '444-333-5545')
insert into #tmp values (12123, 64554, '554-344-3334')

select * from #tmp

select Acctid, Zip, 1 as [order]
from #tmp
union all
select Acctid, phone, 2
from #tmp
order by AcctId, [order]

drop table #tmp

When you have a UNION each corresponding column in the select statements needs to be the same datatype. So you'll most likely need to perform data conversion on some columns.

--------------------
Colt 45 - the original point and click interface

dba321 Hall of Fame Points: 3917 More actions · Answer 9

I'm just not getting through here. I don't want to have to hand code each insert.

I have to select more than 650,000 records from one table. Then I have to cycle through each field of each row to create a new insert in a final table. There's no way I'm gonna have to hand code 650,000 inserts.

I have to do a loop to loop first through each record in table1 which contains 650,000 records, then inside that do another loop and spit out a new record for each column for each record in a final table. So yes, the final table will be huge but this is a process we know will be huge...

philcart SSC-Forever Points: 47794 More actions · Answer 10

In the example I provided there is no hand coding...

--------------------
Colt 45 - the original point and click interface

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 11

I think what Phil was tryong to express here is that you can simply do a multiple UNION ALL query to accomplish hat you need without a cursor.

If you focus on this bit of his example

select Acctid, Zip, 1 as [order]

from #tmp

union all

select Acctid, phone, 2

from #tmp

order by AcctId, [order]

you will see how he achieves exactly what you have asked for.

All you need to do is add UNION ALL clauses for each column in your table.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

detzu SSC Enthusiast Points: 117 More actions · Answer 12

You should use the dictionary of the database to see the structure of the table. Then based on this make a dinamic sql to take only one field from every row .

get row by row from your table.

get column by column from the database structure.
create a dynamic sql to take your field
make your job with this value

Good luck !

PW-201837 SSC-Insane Points: 20805 More actions · Answer 13

Since the general approach here seems to be to use a sledgehammer to drive in a small screw, I'd suggest removing the sledgehammer (T-SQL) and using the appropriate screwdriver (VBScript).

If you insist on nested loops, create a DTS package, add an ActiveX script task, open an ADO recordset and loop through the recordset. Since ADO recordsets give you a handy iterator over the columns contained in each record, you can bypass those nasty T-SQL cursors that don't do what you want.

Then you can go make a nice big bowl of popcorn, and watch all 3 extended DVD's of The Lord Of The Rings while the 650,000 rows churn

Carl Federl One Orange Chip Points: 25384 More actions · Answer 14

"Then you can go make a nice big bowl of popcorn, and watch all 3 extended DVD's of The Lord Of The Rings while the 650,000 rows churn "

PW - your being an optimist

With the Cursor with Cursor solution, you could also watch the entire GodFather series and still have time for 8 hours of sleep.

dba321 - forget the cursor with a cursor solution - It will never work.

SQL = Scarcely Qualifies as a Language