InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 06:34:28 GMT20http://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspxeven my friend gave me a idea to do like this using a split function or DelimitedSplit8K function also[code="sql"]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 ChoiceINTO #tempFROM dbo.split(@questionIdandchoiceid, ',') INSERT INTO @question(alid, QuestionId)SELECT NEWID(), Ques FROM #tempINSERT INTO @ChoiceSELECT QuestionId, alid, t.ChoiceFROM @question qinner join #temp t on q.QuestionId = t.Ques SELECT * FROM @questionSELECT * FROM @ChoiceDROP TABLE #temp[/code]Wed, 26 Sep 2012 10:54:31 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspxthanks Lynn Pettis,ard5karthickfor sharing your knowledges and ideaWed, 26 Sep 2012 10:49:25 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspx[quote][b]ard5karthick (9/26/2012)[/b][hr]Hi Sivaji,The below procedure must solve your problem...Use SSISTestGO-- Exec SplitStringInsert '1-2,2-3,3-5,4-4,14-14,10-9684'Alter Procedure SplitStringInsert (@String Varchar(8000))ASBeginDeclare @BeginingPosition int;Declare @TrunPosition int;Declare @Length int--Set @String = '1-2,2-3,3-5,4-4,14-14,10-9684'Set @BeginingPosition = 0Set @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)BeginSet @TrunPosition = CHARINDEX(',',@String,@BeginingPosition)--Select @TrunPositionInsert into ##SetString (QuestionAnswer)Select SUBSTRING(@String,@BeginingPosition,@TrunPosition)Set @String = SUBSTRING(@String,@TrunPosition+1,@Length)--Select @StringSet @Length = LEN(@String)ENDInsert into ##SetString (QuestionAnswer)Select @StringSELECT NEWID() ID, SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Question into ##QuestionFROM ##SetStringSelect SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Ques,SUBSTRING(QuestionAnswer,CHARINDEX('-',QuestionAnswer,0)+1,@Length) Answer into ##AnswerFrom ##SetStringInsert INTO @question (alid , QuestionId , crdate , IsActive )SELECT ID,Question, NULL,NULLFROM ##Question Insert INTO @Choice (imid , alid , Choiceid ) Select Question, ID,AnswerFrom ##Question join ##Answer on Ques = QuestionSelect * From @ChoiceSelect * From @question--Drop Table ##SetString--Drop table ##Question--Drop table ##AnswerEnd[/quote]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.Wed, 26 Sep 2012 07:16:33 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspxHi Sivaji,The below procedure must solve your problem...Use SSISTestGO-- Exec SplitStringInsert '1-2,2-3,3-5,4-4,14-14,10-9684'Alter Procedure SplitStringInsert (@String Varchar(8000))ASBeginDeclare @BeginingPosition int;Declare @TrunPosition int;Declare @Length int--Set @String = '1-2,2-3,3-5,4-4,14-14,10-9684'Set @BeginingPosition = 0Set @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)BeginSet @TrunPosition = CHARINDEX(',',@String,@BeginingPosition)--Select @TrunPositionInsert into ##SetString (QuestionAnswer)Select SUBSTRING(@String,@BeginingPosition,@TrunPosition)Set @String = SUBSTRING(@String,@TrunPosition+1,@Length)--Select @StringSet @Length = LEN(@String)ENDInsert into ##SetString (QuestionAnswer)Select @StringSELECT NEWID() ID, SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Question into ##QuestionFROM ##SetStringSelect SUBSTRING(QuestionAnswer,1,CHARINDEX('-',QuestionAnswer,0)-1) Ques,SUBSTRING(QuestionAnswer,CHARINDEX('-',QuestionAnswer,0)+1,@Length) Answer into ##AnswerFrom ##SetStringInsert INTO @question (alid , QuestionId , crdate , IsActive )SELECT ID,Question, NULL,NULLFROM ##Question Insert INTO @Choice (imid , alid , Choiceid ) Select Question, ID,AnswerFrom ##Question join ##Answer on Ques = QuestionSelect * From @ChoiceSelect * From @question--Drop Table ##SetString--Drop table ##Question--Drop table ##AnswerEndWed, 26 Sep 2012 03:28:31 GMTard5karthickhttp://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspxPersonally, 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.[code="sql"]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 ChoiceIDfrom dbo.DelimitedSplit8K(@questionIdandchoiceid,',') ds1 cross apply dbo.DelimitedSplit8K(ds1.Item,'-') ds2group by ds1.ItemNumber, ds1.Item)insert into @Question(QuestionID)output inserted.alid, inserted.QuestionIDinto @Qoutselect bd.QuestionIDfrom 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 ChoiceIDfrom dbo.DelimitedSplit8K(@questionIdandchoiceid,',') ds1 cross apply dbo.DelimitedSplit8K(ds1.Item,'-') ds2group by ds1.ItemNumber, ds1.Item)insert into @Choice(alid, ChoiceID)select q.alid, bd.ChoiceIDfrom basedata bd inner join @Qout q on q.QuestionID = bd.QuestionID;select * from @Question;select *from @Choice;[/code]Any questions, check Books Online. If still confused, post back.Wed, 26 Sep 2012 00:41:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1364441-391-1.aspxhere iam having a parameter @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5' from this parameter i just want o insert into two tableDECLARE @questionIdandchoiceid varchar(8000)='1-2,2-3,3-5' [code="sql"]DECLARE @question table( alid uniqueidentifier, QuestionId int, crdate datetime, IsActive bit ) DECLARE @Choice table( imid int, alid uniqueidentifier, Choiceid int )[/code]in that parameter 1-2,2-3the first one will be question id and the second one will be choice id for the questionand 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[code="sql"] 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[/code] Tue, 25 Sep 2012 23:42:58 GMTsivajii