I have complex query which have two table first one have column like this ID,caller_id,datetime ,part1,part2,session
now I have to break part1 into different column and also part2 into different column of another table lime_survey_897455 part1 have max 15 value and they will save in column like this
[897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"897455X7X47" ,"897455X7X51" ,"897455X7X84" ,"897455X7X94" ,"897455X7X104" ,"897455X7X114" ,"897455X7X124" ,"897455X7X134" ,"897455X7X144","897455X7X154",
"897455X7X164","897455X7X174"
similarlly part2
"897455X9X55","897455X11X59" ,"897455X13X63","897455X15X67" ,"897455X24X71" ,"897455X34X184","897455X44X194","897455X54X204" ,"897455X64X214" ,"897455X74X224" ,
"897455X84X234","897455X94X244" ,"897455X104X254","897455X114X264","897455X124X274"
[EDIT - OP has supplied table schema]
CREATE TABLE [dbo].[PMOSurvey]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Caller_id] [varchar](15) NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Part1] [varchar](32) NOT NULL,
[Part2] [varchar](32) NOT NULL,
[Session_Id] [varchar](max) NOT NULL,
CREATE TABLE [dbo].[lime_survey_897455]
(
[ID] [int] IDENTITY(1000,1) NOT NULL,
[token] [varchar](35) NULL DEFAULT (NULL),
[submitdate] [datetime] NULL DEFAULT (NULL),
[lastpage] [int] NULL DEFAULT (NULL),
[startlanguage] [varchar](20) NOT NULL,
[startdate] [datetime] NOT NULL,
[datestamp] [datetime] NOT NULL,
[ipaddr] [text] NULL,
[refurl] [text] NULL,
[897455X7X35] [varchar](1) NULL DEFAULT (NULL),
[897455X7X39] [varchar](1) NULL DEFAULT (NULL),
[897455X7X43] [varchar](1) NULL DEFAULT (NULL),
[897455X7X47] [varchar](1) NULL DEFAULT (NULL),
[897455X7X51] [varchar](1) NULL DEFAULT (NULL),
[897455X7X84] [varchar](1) NULL DEFAULT (NULL),
[897455X7X94] [varchar](1) NULL DEFAULT (NULL),
[897455X7X104] [varchar](1) NULL DEFAULT (NULL),
[897455X7X114] [varchar](1) NULL DEFAULT (NULL),
[897455X7X124] [varchar](1) NULL DEFAULT (NULL),
[897455X7X134] [varchar](1) NULL DEFAULT (NULL),
[897455X7X144] [varchar](1) NULL,
[897455X7X154] [varchar](1) NULL,
[897455X7X164] [varchar](1) NULL,
[897455X7X174] [varchar](1) NULL,
[897455X9X55] [varchar](1) NULL,
[897455X11X59] [varchar](1) NULL,
[897455X13X63] [varchar](1) NULL,
[897455X15X67] [varchar](1) NULL,
[897455X24X71] [varchar](1) NULL,
[897455X34X184] [varchar](1) NULL,
[897455X44X194] [varchar](1) NULL,
[897455X54X204] [varchar](1) NULL,
[897455X64X214] [varchar](1) NULL,
[897455X74X224] [varchar](1) NULL,
[897455X84X234] [varchar](1) NULL,
[897455X94X244] [varchar](1) NULL,
[897455X104X254] [varchar](1) NULL,
[897455X114X264] [varchar](1) NULL,
[897455X124X274] [varchar](1) NULL,
[EDIT 2 - OP has supplied some sample data]
insert into [PMOSurvey] values
('7023123025', '2016-06-08 19:06:14.283', '1:1:1:1:1:1:2:2:1:2:1:1:2:1:1:', '3:3:4:4:2:2:3:5:2:3:3:4:5:3:5:', '0ac72f5e_00002244_57581d1e_42a7_0059'),
('7023225749', '2016-06-08 16:05:10.863', '2:1:1:1:1:2:2:1:2:1:2:1:2:1:1:' ,'1:2:1:2:1:2:5:2:5:2:3:4:2:4:4:', 'ac72f5e_00002244_5757f2a1_1022_005d'),
('7023225745', '2016-06-08 16:05:10.863', '2:1:1:1:1:2:2:1:2:1:2:1:2:1:1:', '1:2:1:2:1:2:5:2:', 'ac72f5e_00002244_5757f2a1_1022_005ee')
insert into [lime_survey_897455] values
('7023123025', '2016-06-08 19:06:14.283', 30, 'hi-IN', '2016-06-08 19:06:14.283', '2016-06-08 19:06:14.283', '10.10.10.10', 'aaaaaaa', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'Y', 'N', 'Y', 'Y', '3', '3', '4', '4', '2', '2', '3', '5', '2', '3', '3', '4', '5', '3', '5'),
('7023225749', '2016-06-08 16:05:10.863', 30, '1', '2016-06-08 16:05:10.863', '2016-06-08 16:05:10.863', '10.10.10.10', 'aaaaaaa', 'N', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'Y', '1', '2', '1', '2', '1', '2', '5', '2', '5', '2', '3', '4', '2', '4', '4'),
('7023225745', '2016-06-08 16:05:10.863', 23, 'en-IN', '2016-06-08 16:05:10.863', '2016-06-08 16:05:10.863', '10.10.10.10', 'aaaaaaa', 'N', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'N', 'Y', 'Y', '1', '2', '1', '2', '1', '2', '5', '2', null, null, null, null, null, null, null)
What I have tried:
sp_helptext INSERTData_lime_survey_897455
alter proc INSERTData_lime_survey_897455
as
begin
truncate table lime_survey_897455
DECLARE @cnt INT = 1;
declare @cnt1 int;
declare @loop1 int=1;
declare @loop2 int=1;
set @cnt1 =(select max(ID) from PMOSurvey)
while @cnt<=@cnt1
begin
DECLARE @question VARCHAR(100)
set @question =(select part1 from PMOSurvey where ID=@cnt )
DECLARE @question1 VARCHAR(100)
set @question1 =(select part2 from PMOSurvey where ID=@cnt)
insert into lime_survey_897455([897455X7X35] ,[897455X7X39] ,"897455X7X43" ,"897455X7X47" ,"897455X7X51" ,"897455X7X84" ,"897455X7X94" ,"897455X7X104" ,"897455X7X114" ,"897455X7X124" ,"897455X7X134" ,"897455X7X144","897455X7X154","897455X7X164","897455X7X174" ,"897455X9X55","897455X11X59" ,"897455X13X63","897455X15X67" ,"897455X24X71" ,"897455X34X184","897455X44X194","897455X54X204" ,"897455X64X214" ,"897455X74X224","897455X84X234","897455X94X244" ,"897455X104X254","897455X114X264","897455X124X274",token,submitdate,lastpage,startlanguage,startdate,
datestamp,ipaddr,refurl)
while @loop1<30
begin
SELECT case when (SUBSTRING(@question,@loop1, CHARINDEX(':', @question) - 1))=1 then 'Y' else 'N' end
set @loop1=@loop1+2;
end
while @loop2<30
begin
select SUBSTRING(@question1,@loop2, CHARINDEX(':', @question1) - 1)
set @loop2=@loop2+2;
end
(select Caller_id from PMOSurvey where ID=@cnt)
(select Date_time from PMOSurvey where ID=@cnt )
(select (LEN(part1)+LEN(part2))/2 from PMOSurvey where ID=@cnt)
(ISnull(select s.Lang_Mode from Saved_Lang s join PMOSurvey p on s.Phone_No=p.Caller_id where s.App_Name='RATE_My_GOV' and p.ID=@cnt),1)
(select Date_time from PMOSurvey where ID=@cnt )
(select Date_time from PMOSurvey where ID=@cnt ),
'10.10.10.10' as ipaddr,
'aaabbbb' as refurl
set @cnt=@cnt+1
end
end