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
NextCase @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. -
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 ENDIt'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 !!!** -
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
-
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! ??
-
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
-
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....
Next
...
END
-
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.
-
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.
-
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 -
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...
-
In the example I provided there is no hand coding...
--------------------
Colt 45 - the original point and click interface -
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.
-
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
! -
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
-
"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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply