how to write insert query for this two table with only one parameter?

alid QuestionId crdate IsActive
BB83F8E9 1 null null
9EB1E08563 2 null null
SB83F8ERE 3 null null

imid alid Choiceid
1 BB83F8E9 2
2 9EB1E08563 3
3 SB83F8ERE 5

DECLARE @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5' ;

DECLARE @Qout table(alid uniqueidentifier, QuestionID int);

DECLARE @question table
(
alid uniqueidentifier default newid(),
QuestionID int,
crdate datetime null,
IsActive bit null
);

DECLARE @Choice table
(
imid int identity(1,1),
alid uniqueidentifier,
ChoiceID int
);

with basedata as (
select
max(case ds2.ItemNumber when 1 then cast(ds2.Item as int) else null end) as QuestionID,
max(case ds2.ItemNumber when 2 then cast(ds2.Item as int) else null end) as ChoiceID
from
dbo.DelimitedSplit8K(@questionIdandchoiceid,',') ds1
cross apply dbo.DelimitedSplit8K(ds1.Item,'-') ds2
group by
ds1.ItemNumber,
ds1.Item
)
insert into
@Question(QuestionID)
output
inserted.alid, inserted.QuestionID
into
@Qout
select
bd.QuestionID
from
basedata bd;

with basedata as (
select
max(case ds2.ItemNumber when 1 then ds2.Item else null end) as QuestionID,
max(case ds2.ItemNumber when 2 then ds2.Item else null end) as ChoiceID
from
dbo.DelimitedSplit8K(@questionIdandchoiceid,',') ds1
cross apply dbo.DelimitedSplit8K(ds1.Item,'-') ds2
group by
ds1.ItemNumber,
ds1.Item
)
insert into
@Choice(alid, ChoiceID)
select
q.alid,
bd.ChoiceID
from
basedata bd
inner join @Qout q
on q.QuestionID = bd.QuestionID;

select * from @Question;
select *from @Choice;

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

DECLARE @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5'

DECLARE @question table(
alid uniqueidentifier,
QuestionId int,
crdate datetime,
IsActive bit
)

DECLARE @Choice table(
imid int,
alid uniqueidentifier,
Choiceid int
)

SELECT LEFT(val, CHARINDEX('-', val)-1) AS Ques
, STUFF(val, 1, CHARINDEX('-', val), '') AS Choice
INTO #temp
FROM dbo.split(@questionIdandchoiceid, ',')

INSERT INTO @question(alid, QuestionId)
SELECT NEWID(), Ques FROM #temp

INSERT INTO @Choice
SELECT QuestionId, alid, t.Choice
FROM @question q
inner join #temp t on q.QuestionId = t.Ques

SELECT * FROM @question
SELECT * FROM @Choice

DROP TABLE #temp