Cursor Help

Question

Cursor Help

Alan Naylor

SSC-Addicted

Points: 469
More actions
December 3, 2008 at 7:38 am

#92837

I have a one column table. The data in that column has 1142 characters. It is fixed delimited data. To make it more dynamic for parsing the data I have created a table that has the fieldname, fieldlength and field size. I want to create a cursor that will parse this data into the correct fields into a table. When I run this it puts the data into the first field. Lets say there are 50 records in the One column table. Well it puts the first 50 rows into the new table but it starts the second field at 51 and so on and so forth.
Declare DDOKRaw cursor for
Select [field name],startingposition, fieldlength from dbo.TablefieldPositionLength
Declare @fn varchar(128)
Declare @sp-2 varchar(128)
Declare @fl varchar(128)
Open DDOKRAW
Fetch Next From DDOKRaw into @fn, @sp-2, @fl
set @rownum = 1
While @@Fetch_Status = 0
Begin
EXEC('insert into migr0173 (' + @fn + ') select top 1 substring(data,' + @sp-2 + ', ' + @fl + ') as ' + @fn + ' from dbo.MIGR0173_20080917061003')
Fetch Next From DDOKRaw into @fn, @sp-2, @fl
end
Close DDOKRaw
Deallocate DDOKRaw

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

May I ask why you have a one column table that contains data that should be in multiple columns?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 2

When you say the data is "fixed delimited", do you mean that it uses fixed width for columns? For example, the first 10 characters are column 1, characters 11 through 25 are column 2, and so on?

If so, and you have a definition for what the widths are, why do you need a cursor for it? Why not just select the data all at once?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

If you have to do it this way, then something like this should suffice:

[font="Courier New"]-- Make some sample data

DROP TABLE #TablefieldPositionLength

CREATE TABLE #TablefieldPositionLength ([field name] VARCHAR(30), startingposition INT, fieldlength INT)

INSERT INTO #TablefieldPositionLength ([field name], startingposition, fieldlength)

SELECT 'Firstname', 1, 20 UNION ALL

SELECT 'Surname', 22, 20 UNION ALL

SELECT 'Address', 43, 30

DROP TABLE dbo.MIGR0173_20080917061003

CREATE TABLE dbo.MIGR0173_20080917061003 ([Data] VARCHAR (200))

INSERT INTO dbo.MIGR0173_20080917061003 ([Data])

SELECT 'John Irving The Hotel New Hampshire ' UNION ALL

SELECT '11111111111111111111 22222222222222222222 333333333333333333333333333333' UNION ALL

SELECT '44444444444444444444 55555555555555555555 666666666666666666666666666666' UNION ALL

SELECT '77777777777777777777 88888888888888888888 999999999999999999999999999999'

-- Run the script against the sample data

DECLARE @cSQL VARCHAR(1000)

SET @cSQL = 'SELECT'

SELECT @cSQL = @cSQL + ' ' + [field name] + ' = SUBSTRING([Data],'

+ CAST(startingposition AS VARCHAR(3)) + ', '

+ CAST(fieldlength AS VARCHAR(3)) + '),' + CHAR(10)

FROM #TablefieldPositionLength

DROP TABLE ##Temp

SET @cSQL = REVERSE(STUFF(REVERSE(@cSQL),1,2,'')) + 'INTO ##Temp FROM dbo.MIGR0173_20080917061003'

EXEC (@cSQL)

SELECT * FROM ##Temp

[/font]

- but answering Jack & GSquared's questions could result in a far simpler procedure.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Alan! How about a little test data? This isn't difficult but it's impossible to solve in a reasonable fashion without a copy of your field table and a least a couple of rows of test data. Please see the link in my signature line below for how to format and provide both to get the best answer possible.

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

Alan Naylor SSC-Addicted Points: 469 More actions · Answer 5

Ths is the correct solution.

Declare DDOKRaw cursor for

Select [field name],startingposition, fieldlength from dbo.TablefieldPositionLength

DECLARE @insert_part varchar(4000)

DECLARE @value_part varchar(4000)

DECLARE @loop int

Declare @fn varchar(128)

Declare @sp-2 varchar(128)

Declare @fl varchar(128)

Open DDOKRAW

Fetch Next From DDOKRaw into @fn, @sp-2, @fl

set @loop = 1

While @@Fetch_Status = 0

Begin

if @loop=1

begin

select @insert_part='insert into migr0173 (' + @fn

select @value_part='select substring(data,'+ @sp-2 + ', ' + @fl + ') as ' + @fn

end

else

begin

select @insert_part=@insert_part + ',' + @fn

select @value_part=@value_part + ',' + 'substring(data,'+ @sp-2 + ', ' + @fl + ') as ' + @fn

end

set @loop=@loop+1

Fetch Next From DDOKRaw into @fn, @sp-2, @fl

end

select @insert_part=@insert_part + ') '

select @value_part=@value_part + ' from dbo.MIGR0173_20080917061003'

Close DDOKRaw

Deallocate DDOKRaw

exec (@insert_part + @value_part)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Alan Naylor (12/8/2008)
Ths is the correct solution.

No... it's not... not if it has a cursor in it. 😉 But, if you would rather do that than posting the info I asked for, you go right ahead. 😛

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

Alan Naylor SSC-Addicted Points: 469 More actions · Answer 7

Sorry. But this is what I was looking for and it works.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

No apology necessary... I'm not the one that just put a cursor into production.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Alan Naylor (12/8/2008)
Ths is the correct solution.
Declare DDOKRaw cursor for
Select [field name],startingposition, fieldlength from dbo.TablefieldPositionLength
DECLARE @insert_part varchar(4000)
DECLARE @value_part varchar(4000)
DECLARE @loop int
Declare @fn varchar(128)
Declare @sp-2 varchar(128)
Declare @fl varchar(128)
Open DDOKRAW
Fetch Next From DDOKRaw into @fn, @sp-2, @fl
set @loop = 1
While @@Fetch_Status = 0
Begin
if @loop=1
begin
select @insert_part='insert into migr0173 (' + @fn
select @value_part='select substring(data,'+ @sp-2 + ', ' + @fl + ') as ' + @fn
end
else
begin
select @insert_part=@insert_part + ',' + @fn
select @value_part=@value_part + ',' + 'substring(data,'+ @sp-2 + ', ' + @fl + ') as ' + @fn
end
set @loop=@loop+1
Fetch Next From DDOKRaw into @fn, @sp-2, @fl
end
select @insert_part=@insert_part + ') '
select @value_part=@value_part + ' from dbo.MIGR0173_20080917061003'
Close DDOKRaw
Deallocate DDOKRaw
exec (@insert_part + @value_part)

OK, that might be the correct solution. OR..., this might be the correct solution:

DECLARE @insert_part varchar(4000)

DECLARE @value_part varchar(4000)

DECLARE @loop int

Select @insert_part=Coalesce(@insert_part + ',' + [field name]

, 'insert into migr0173 (' + [field name])

, @value_part=Coalesce(@value_part + ',' + 'substring(data,'

+ startingposition + ', ' + fieldlength + ') as ' + [field name]

, 'select substring(data,'+ startingposition + ', ' + fieldlength + ') as ' + [field name])

From dbo.TablefieldPositionLength

Select @insert_part=@insert_part + ') '

, @value_part=@value_part + ' from dbo.MIGR0173_20080917061003'

exec (@insert_part + @value_part)

One of these is about 100x faster than the other, so you be the judge on which is correct. 😀

Seriously, dude, there is just nothing in this problem or its solution that needs a cursor.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

If the [Field Length] isn't character based (and, it shouldn't be), you'll probably have to add an explicit conversion to [Field Length]... but it is the classic way to do this without a cursor.

It also helps the DBA's blood pressure.

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