Need help with a while and cursor

Question

Need help with a while and cursor

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

Steve T (11/11/2008)
Sounds like a bank/credit feed file. There's probably no data structure other than 2 columns. 1 for the header record you manually added and the other is probably the actual data, but this is just a guess.
Assuming your data is similar to this...
HTEST1
Record1
Record2
Record3
Record4
Record5
Record6
Record7
Record8
Record9
HTEST2
Record1
Record2
Record3
Record4
Record5
Record6
Record7
Record8
Record9
HTEST3
Record1
Record2
Record3
Record4
Record5
Record6
Record7
Record8
Record9
This is what I did:
CREATE TABLE #InputData
(Cnt INT IDENTITY(1,1), Data VARCHAR(1000))
DECLARE MyCursor CURSOR
FOR SELECT Cnt, Data FROM #InputData ORDER BY Cnt
OPEN MyCursor
DECLARE @Cnt INT, @data VARCHAR(1000), @Header VARCHAR(10)
FETCH NEXT FROM MyCursor
INTO @Cnt, @data
WHILE @@FETCH_STATUS = 0
BEGIN
IF SUBSTRING(@Data, 1, 1) = 'H'
BEGIN
SET @Header = SUBSTRING(@Data, 2, 10)
INSERT INTO #Final
SELECT 'H', NULL, @Header
END
ELSE
BEGIN
INSERT INTO #Final
SELECT 'D', @data, @Header
END
FETCH NEXT FROM MyCursor
INTO @Cnt, @data
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT * FROM #Final

No... not guaranteed to work because there's nothing to guarantee the order of the data.

--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)

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

SQL DBA 808 (11/11/2008)
Steve T: Yes. That's very close to what I need to do. Thank you.
Garadin: I will read that and see if I can apply it to this situation before my management kills me for not having this process completed sooner.

Heh... been there, done that. If you could take the time to post the table structure and some sample data using the methods found in the link in my signature below, maybe we could keep management from killing you. 🙂

--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)

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

I don't think you need a cursor & while statement either, but as others have said, we need the table structure before we can accurately help you. Don't assume the SQL given before will actually resolve your problem. Especially if you're just assuming the records following the header happen to be associated with the header.

Remember what they say about assumptions. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

steve block SSCertifiable Points: 7698 More actions · Answer 4

In what format are you getting the data and how is it inserted into Sql Server? Since there is no guaranteed order of the data, the time to assign an "order" field would be when it is inserted into a Sql-Server table.

Steve

thecodeguys Ten Centuries Points: 1076 More actions · Answer 5

I haven't given up, I am just taking some time to read the posts that were given. I'll give an update when I have one.