Loop through each record and then each field within each record
-
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] -
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.
-
" ...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
-
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
-
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)
Intro to Tally Tables and Functions -
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!!!
-
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)
Intro to Tally Tables and Functions
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply