April 20, 2015 at 11:27 pm
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('B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯')
INSERT INTO stagingtemp VALUES('B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯118223766¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯160226298¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯160465122¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯161124966¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯172003797¯¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯202550454¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯202556214¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯')
INSERT INTO stagingtemp VALUES('B¯211183371¯¯ ¯ ¯ ¯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,
[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
April 21, 2015 at 3:33 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply