ard5karthick (9/26/2012)

---

Hi Sivaji,

The below procedure must solve your problem...

Use SSISTest

GO

-- Exec SplitStringInsert '1-2,2-3,3-5,4-4,14-14,10-9684'

Alter Procedure SplitStringInsert (@String Varchar(8000))

AS

Begin

Declare @BeginingPosition int;

Declare @TrunPosition int;

Declare @Length int

--Set @String = '1-2,2-3,3-5,4-4,14-14,10-9684'

Set @BeginingPosition = 0

Set @Length = LEN(@String)

Create Table ##SetString(QuestionAnswer Varchar(8000))

DECLARE @question table

(

alid uniqueidentifier,

QuestionId int,

crdate datetime,

IsActive bit

)

DECLARE @Choice table

(

imid int,

alid uniqueidentifier,

Choiceid int

)

While (CHARINDEX(',',@String,0) <> 0)

Begin

Set @TrunPosition = CHARINDEX(',',@String,@BeginingPosition)

--Select @TrunPosition

Insert into ##SetString (QuestionAnswer)

Select SUBSTRING(@String,@BeginingPosition,@TrunPosition)

Set @String = SUBSTRING(@String,@TrunPosition+1,@Length)

--Select @String

Set @Length = LEN(@String)

END

Insert into ##SetString (QuestionAnswer)

Select @String

SELECT NEWID() ID, SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Question

into ##Question

FROM ##SetString

Select SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Ques,

SUBSTRING(QuestionAnswer,CHARINDEX('-',QuestionAnswer,0)+1,@Length) Answer

into ##Answer

From ##SetString

Insert INTO @question (alid ,

QuestionId ,

crdate ,

IsActive )

SELECT ID,Question, NULL,NULL

FROM ##Question

Insert INTO @Choice

(imid ,

alid ,

Choiceid )

Select Question, ID,Answer

From ##Question join ##Answer on Ques = Question

Select * From @Choice

Select * From @question

--Drop Table ##SetString

--Drop table ##Question

--Drop table ##Answer

End