Hi All,

I am having 2 tables one is staging temp and another is main import table.

In my staging table there are 3 column Col001,Id,Loaddate

in Col001 column data are present with '' delemeter.

I ma having function which is used to load data from staging to import table using one function.

this function create a insert statement.

My Existing function

-- Description:    To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter] 
(
    @fieldname varchar(max)
    ,@delimiter varchar(max)
    ,@delimiter_count int
)
RETURNS varchar(max)
AS
BEGIN
    -- variable declaration
    declare @first_index varchar(max)
    ,@next_index varchar(max)
    ,@sql_statement varchar(max)
    ,@term_statement varchar(max)
    ,@length varchar(max)
    ,@iterator int

    -- initialization
    set @next_index=-1
    set @iterator=1
    set @sql_statement=' '

    -- code
    while(@iterator<=@delimiter_count)
    begin
        set @first_index=@next_index+'+1'
        set @next_index='CHARINDEX('''+@delimiter+''','+@fieldname+','+@first_index+')'
        if (@iterator=@delimiter_count)
        begin
        set @next_index='LEN('+@fieldname+')+1'
        end
        set @length=@next_index+'-('+@first_index+')'
        set @term_statement='SubString('+@fieldname+','+@first_index+','+@length+')'
        set @term_statement='case when '+@next_index+'>0 then '+@term_statement+' else ''nothing'' end as Column'+CONVERT(varchar(max),@iterator) 
        if (@iterator=1) 
        begin
            set @sql_statement=@term_statement
        end
        else
        begin
            set @sql_statement=@sql_statement+', '+@term_statement
        end

        set @iterator=@iterator+1
    end


    -- Return the result of the function
    RETURN @sql_statement

END

it is working fine with almost all the table but not working with this one

create table stagingtemp
(
COL001 varchar(4000),
id int identity(1,1)
lODDATE DATETIME DEFAULT GETDATE()
)

INSERT INTO stagingtemp VALUES('B080623719 Y YY                ')
INSERT INTO stagingtemp VALUES('B106618392 Y                ')
INSERT INTO stagingtemp VALUES('B118223766 Y                ')
INSERT INTO stagingtemp VALUES('B160226298 Y                ')
INSERT INTO stagingtemp VALUES('B160465122 Y                ')
INSERT INTO stagingtemp VALUES('B161124966 Y                ')
INSERT INTO stagingtemp VALUES('B172003797Y                ')
INSERT INTO stagingtemp VALUES('B202550454 Y                ')
INSERT INTO stagingtemp VALUES('B202556214 Y                ')
INSERT INTO stagingtemp VALUES('B211183371 Y                ')

i am having 31 column in my import table.

above function shuold create insert into stagingtemp

select statement.

Import table script 

CREATE TABLE [dbo].[ImportBBxFBCOB](
[BILLTYPE] [varchar](8000) NULL, [PRONO] [varchar](8000) NULL, [FS1] [varchar](8000) NULL,
[FOODS] [varchar](8000) NULL,
[POISON] [varchar](8000) NULL,
[POISON2] [varchar](8000) NULL, [HAZMAT] [varchar](8000) NULL, [FREEZABLE] [varchar](8000) NULL,
[LIFTGATE] [varchar](8000) NULL, [STTRUCK] [varchar](8000) NULL,
[DOCKHOLD] [varchar](8000) NULL, [RESIDENTIAL] [varchar](8000) NULL,
[ID] [varchar](8000) NULL, [EXPEDITED] [varchar](8000) NULL,
[INBOND] [varchar](8000) NULL, [DECLARED] [varchar](8000) NULL, [PROTECTLOAD] [varchar](8000) NULL,
[PUP] [varchar](8000) NULL,
[PALLETRATE] [varchar](8000) NULL, [QUOTE] [varchar](8000) NULL, [POSSGSDS] [varchar](8000) NULL,
[NOAUTORATE] [varchar](8000) NULL,
[WEEKENDEXPRESS] [varchar](8000) NULL, [MBULK] [varchar](8000) NULL, [ACCONLY] [varchar](8000) NULL,
[FLAT] [varchar](8000) NULL, [MINIMUM] [varchar](8000) NULL, [OVERSIZED] [varchar](8000) NULL,
[MCUSTS] [varchar](8000) NULL, [UNUSED2_4] [varchar](8000) NULL, [FS2] [varchar](8000) NULL
) 

I am unable to get correct statement with above function.

please help to get the same.

regards,

Vipin jha

What error are you getting?

incomplete insert statement are created .

due to that insert fails

What should be your expected output?

You can this alternate version as well and see if it works

Parse Values UDF

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

and use it as below

INSERT dbo.ImportBBxFBCOB
SELECT MAX(CASE WHEN f.ID = 1 THEN f.Val END) AS Col1,
MAX(CASE WHEN f.ID = 2 THEN f.Val END) AS Col2,
MAX(CASE WHEN f.ID = 3 THEN f.Val END) AS Col3,
MAX(CASE WHEN f.ID = 4 THEN f.Val END) AS Col4,
MAX(CASE WHEN f.ID = 5 THEN f.Val END) AS Col5,
..
FROM stagingtemp s
CROSS APPLY dbo.ParseValues (s.COL001,'-')f
GROUP BY s.COL001

No I can not use above function.

suppose I ma having table with col001,in Col001 column data are present with '' delemeter.

like this 

B080623719 Y YY                

by the use of my function i am getting insert statement of most of the table, but unable to get with above data