DECLARE @str as varchar(max)
SET @str = ‘Prakash,Sanket,Jigar,JD’
SELECT value as GiveColumName from dbo.split(@str,’,’)
Split function used to insert to multiple records into tables…. see below procedure
INSERT INTO BoqThermoDrawing
SELECT @BoqNo,@RevisionNo,@RootPath+’\’+value from dbo.Split(@FileNames,’/’)
—————————————————————–
Create function in sql server… split after that u can use above function
USE [DataBaseName]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 03/04/2010 11:55:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([ID] int,[Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
–Initialize
set @NextString = ”
set @CommaCheck = right(@String,1)
–Check for trailing Comma, if not exists, INSERT
–if (@CommaCheck @Delimiter )
set @String = @String + @Delimiter
–Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
–Loop while there is still a comma in the String of levels
Declare @sr as int
set @sr =0
while (@pos 0)
begin
set @NextString = substring(@String,1,@Pos – 1)
set @sr = @sr + 1
insert into @ValueTable ([id], [Value]) Values (@sr , @NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
return
end
