Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: Loop through each record and then each field within each record

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dba321
    I need to essentially do 2 loops.
    Light fuse...
    Quote Originally Posted by dba321
    One loops through each record and then inside each record row
    ...stand back...
    Quote Originally Posted by dba321
    I want to perform an insert on each column.
    ...cover ears...
    Quote Originally Posted by dba321
    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
    KABOOM!!!!!!!!!!

    Why are you doing this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you trying to normalize this beast? If so, I'd do one insert operation per column in the original table. Fast, easy, clear, simple... What's not to like?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •