dba123
Computer
- Dec 15, 2005
- 3
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.
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.