Cursor Help

4 Dec 2008 12:52 AM

anaylor01

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

--
Alan.

Message posted via http://www.sqlmonster.com

4 Dec 2008 3:10 AM

Jonathan Kehayias

The problem is that you are inserting one row per loop by using top 1. Can
you post an example set of data, and a few of the rows from your parsing
table? You can probably do this without using a cursor at all if you write
the code out correctly. A set based operation will outperform the cursor,
and likely require less code.

--
Jonathan Kehayias
SQL Server MVP
http://jmkehayias.blogspot.com
http://www.sqlclr.net

Show quoteHide quote

"anaylor01" <u11795@uwe> wrote in message news:8e1e23737d7ad@uwe...
>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
>
> --
> Alan.
>
> Message posted via http://www.sqlmonster.com
>

4 Dec 2008 3:50 AM

Plamen Ratchev

Try this (SQL server 2005/2008):

DECLARE @cols NVARCHAR(1000);
SELECT @cols = STUFF((SELECT '],[' + [field name]
                       FROM TablefieldPositionLength
                       ORDER BY '],[' + [field name]
                       FOR XML PATH('')), 1, 2, '') + ']';

DECLARE @query NVARCHAR(2000);
SELECT @query =
        STUFF((SELECT ',SUBSTRING(data, ' +
                      CAST(startingposition AS VARCHAR(10)) + ', ' +
                      CAST(fieldlength AS VARCHAR(10)) + ') AS [' +
[field name] + ']'
               FROM TablefieldPositionLength
               FOR XML PATH('')), 1, 1, '');

DECLARE @sql NVARCHAR(4000);
SET @sql = N'INSERT INTO migr0173 (' + @cols + N') ' +
            N'SELECT ' + @cols + N' ' +
            N'FROM ( SELECT ' + @query +
                  N' FROM MIGR0173_20080917061003 ) AS T;';

EXEC(@sql);

--
Plamen Ratchev
http://www.SQLStudio.com

4 Dec 2008 4:15 AM

Plamen Ratchev

BTW, you can accomplish this task without coding. Just use the BCP
utility to get the data out to a flat file, then create format file
based on the column definitions in table TablefieldPositionLength and
use BCP to import the file into the target table.

More about BCP:
http://msdn.microsoft.com/en-us/library/ms162802.aspx

SSIS can do that transformation too:
http://www.microsoft.com/technet/prodtechnol/sql/2005/intro2is.mspx

--
Plamen Ratchev
http://www.SQLStudio.com

5 Dec 2008 5:39 AM

SB

Show quote Hide quote

On Dec 4, 6:52 am, "anaylor01" <u11795@uwe> wrote:
> 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
>
> --
> Alan.
>
> Message posted viahttp://www.sqlmonster.com

Interesting approach. However I also faced a similar problem recently
and would like to relate. The file was NOT fixed length and my only
choice was to load one line from the text file as a column in a table.
I used ssis to load the data into a table. Your file is fixed length
therefore ssis is another choice to import the data into server as
fixed column file. Then once loaded how you parse the data is a
different issue. In my case data was separated by a space for some
lines. Once I got a cursor pointed to that line I just strip out one
space and get data for one column at a time. Once I had all the
columns I just did a simple insert into the destination table. Before
opening the cursor I also did some quick search into that table (using
relational queries) and picked out different bits of data that were
scattered all over the file and once I got all the columns I inserted
it as a row into the table.

8 Dec 2008 6:19 PM

anaylor01 via SQLMonster.com

This is the solution that works:

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 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
exec (@insert_part + @value_part)

SB wrote:
Show quoteHide quote

>> 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
>[quoted text clipped - 27 lines]
>>
>> Message posted viahttp://www.sqlmonster.com
>
>Interesting approach. However I also faced a similar problem recently
>and would like to relate. The file was NOT fixed length and my only
>choice was to load one line from the text file as a column in a table.
>I used ssis to load the data into a table. Your file is fixed length
>therefore ssis is another choice to import the data into server as
>fixed column file. Then once loaded how you parse the data is a
>different issue. In my case data was separated by a space for some
>lines. Once I got a cursor pointed to that line I just strip out one
>space and get data for one column at a time. Once I had all the
>columns I just did a simple insert into the destination table. Before
>opening the cursor I also did some quick search into that table (using
>relational queries) and picked out different bits of data that were
>scattered all over the file and once I got all the columns I inserted
>it as a row into the table.

--
Alan.

Message posted via http://www.sqlmonster.com