Loop through each record and then each field within each record

Question

Loop through each record and then each field within each record

Viewing 7 posts - 16 through 21 (of 21 total)

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

JacekO SSCertifiable Points: 6610 More actions · Answer 1

I don't understand this fascination with cursors. I have seen people using them in cases where a much simpler plain SQL code will do.

If one has a proper database design there is no need to use them. Period.

And in many cases if there is a need to loop thru records in your database it can almost always be achieved thru simple WHILE loop. Which is always much faster then the cursor. I do understand there are scenarios when you have to use them but some people are just 'cursor happy'. (I have seen a cursor inside an UDF)

In this case (if the problem is specific to one table and not a generic issue with all the tables in the database) I agree with the UNION method proposed by Phil. If you have to deal with multiple tables you may have to get the field names from syscolumns and create dynamic SQL to get what you want.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

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

You have to understand, this is not a database, it is just some temp tables I'll create to create my final table wich will then be used to create a flat file. The stupid ERP system needs my flat file....my job is to create this flat file for that system then they're using the stupid ERP GUI with my flat file. The ERP system requires that each column in a row be a separate record in order to use the Gui to update the rows in the ERP system. My solution is just a process that creates a flatfile so the assessment of a correctly designed database has no play here, my goal is to create an odd flat file that a nother ERP system needs.

philcart SSC-Forever Points: 47796 More actions · Answer 3

" ...My solution is just a process that creates a flatfile so the assessment of a correctly designed database has no play here, my goal is to create an odd flat file that a nother ERP system needs..."

The solution I suggested provides just that without the use of cursors, and having to work out how to loop through each field in the cursor.

Just insert the result from the unioned select statements into your "temp" tables and the export that table to a flat file.

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

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

I have a few questions on your solution

set nocount on

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

>>>  what if I have 650,000 records in my result set, I don't want to have to what I call hand code the values below like you show...am I missing something?

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

>>  Isn't the union just taking all the inserts and combining them together?  I guess I don't understand.

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

drop table #tmp

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

dba321,

The Inserts that Phil provided are just test code to simulate your much larger table (or result set, whatever)! You don't need any of those Inserts and you certainly don't need the CREATE TABLE statement. Since you're new to this forum, I'll tell you that lot's of us provide test code and test data to demonstrate the solution without actually having access to your live data. Its just Test Code!

The UNION code Phil provided will make your flat file from your large 650k record table (result set, whatever)... just change the #tmp to the name of YOUR table and let it rip... just the SELECT/UNION part. It'll work! Trust us and try it...

If you were to provide the code that produces the result set, perhaps we could show you a nice little trick that would keep you from having to include the result set code 3 times in the unioned query, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

I'm sorry, thanks Jeff. Sorry Phil. I thank you for your inputs. I'll try your solution Phil...now that I see what you're doing there. Thanks!!!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

No need to be sorry... most of us have made the same mistake early in our SQL careers. Those that say they didn't... are lying.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)