September 25, 2012 at 11:42 pm
here iam having a parameter @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5' from this parameter i just want o insert into two table
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
)
in that parameter 1-2,2-3
the first one will be question id and the second one will be choice id for the question
and in the @choice table we want to insert alid as a foregin key also
and all the other column will be null (crdate ,IsActive )
i am trying to insert like this
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
September 26, 2012 at 12:41 am
Personally, I'd like to know what you have done so far, but here is a solution anyway. I know you have been give some of the code used already, if not you can find here on ssc.
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;
Any questions, check Books Online. If still confused, post back.
September 26, 2012 at 3:28 am
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
September 26, 2012 at 7:16 am
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
I will put my solution up againsts this one any day. Guess tonight I work on a 1,000,000 row test to test them both.
September 26, 2012 at 10:49 am
thanks
Lynn Pettis,
ard5karthick
for sharing your knowledges and idea
September 26, 2012 at 10:54 am
even my friend gave me a idea to do like this using a split function or DelimitedSplit8K function also
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply