Cursor Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor Help

Author

Topic

anaylor01
Starting Member

28 Posts

Posted - 2008-12-03 : 09:33:46

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 varchar(128)
Declare @fl varchar(128)
Open DDOKRAW
Fetch Next From DDOKRaw into @fn, @sp, @fl
set @rownum = 1
While @@Fetch_Status = 0
Begin
EXEC('insert into migr0173 (' + @fn + ') select top 1 substring(data,' + @sp + ', ' + @fl + ') as ' + @fn + ' from dbo.MIGR0173_20080917061003')
Fetch Next From DDOKRaw into @fn, @sp, @fl
end
Close DDOKRaw
Deallocate DDOKRaw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 09:39:01

cant you go for a set based approach? May be you could illustrate your reqmnt with some sample data so that we can try to give sample query.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-03 : 09:55:59

Also, Why do you want to make it more dynamic for parsing ? Does the startingposition, fieldlength etc change very often ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-03 : 14:23:12

[code]
Declare DDOKRaw cursor for
Select [field name],startingposition, fieldlength from dbo.TablefieldPositionLength

DECLARE @insert_part varchar(2000)
DECLARE @value_part varchar(2000)
DECLARE @loop int
Declare @fn varchar(128)
Declare @sp varchar(128)
Declare @fl varchar(128)
Open DDOKRAW
Fetch Next From DDOKRaw into @fn, @sp, @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 + ', ' + @fl + ') as ' + @fn
end
else
begin
select @insert_part=@insert_part + ',' + @fn
select @value_part=@value_part + ',' + 'substring(data,'+ @sp + ', ' + @fl + ') as ' + @fn
end
set @loop=@loop+1
Fetch Next From DDOKRaw into @fn, @sp, @fl
end
select @insert_part=@insert_part + ') '
select @value_part=@value_part + ' from dbo.MIGR0173_20080917061003'
Close DDOKRaw
Deallocate DDOKRaw
print @insert_part
print @value_part
exec (@insert_part + @value_part)
[/code]

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

anaylor01
Starting Member

28 Posts

Posted - 2008-12-04 : 10:35:30

I changed the VARCHAR(2000) to VARCHAR(4000) and it worked freaking awesome. You are the man WEBFRED. Thank you thank you thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-05 : 15:02:49

my pleasure