I have a nested loop that checks all the names and dates from descript column from table tmp13 and stores them as individual rows in other table (tmp14). The problem is that the while loop is executing for a long time. I don't know how to make it run faster. I have tried some suggests already from previous post, but I haven't been very successful. Can anyone give some suggestion to approach this horrible issue.
Here is my code checking the descript columns for names and date. Descript is a text column and can have multiple names and dates. I want to store those names and dates in separate rows.
DECLARE @Id INT
DECLARE @count INT
DECLARE @product_num INT
DECLARE @REQUESTED VARCHAR(50)
DECLARE @FirstDate VARCHAR(255)
DECLARE @RequestedBy VARCHAR(255)
DECLARE @name NVARCHAR(256)
DECLARE @date NVARCHAR(256)
DECLARE @desc NVARCHAR(256)
DECLARE @dateposition INT
DECLARE @nameposition INT
DECLARE @nameend INT
SELECT @count = MAX(id)
FROM #TMP13
SET @id = 1;
WHILE (@id <= @count)
BEGIN
SELECT @desc = descript FROM #TMP13 WHERE Id = @Id
SELECT @product_num = p_Num FROM #TMP13 WHERE Id = @Id
SELECT @REQUESTED = REQUESTED FROM #TMP13 WHERE Id = @Id
SELECT @FirstDate = DATE1 FROM #TMP13 WHERE Id = @Id
SELECT @RequestedBy = BY1 FROM #TMP13 WHERE Id = @Id
while (patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@desc) > 0)
begin
set @dateposition = patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9]%',@desc)
set @date = SUBSTRING(@desc,@dateposition,10)
set @nameposition = CHARINDEX('-', @desc)+2
set @nameend = CHARINDEX(' ', @desc, @nameposition)+1
set @name = SUBSTRING(@desc,@nameposition,@nameend-@nameposition)
insert into #TMP14
values (@Id,@product_num,@REQUESTED, @FirstDate ,@RequestedBY, @date, @name)
set @desc = SUBSTRING(@desc,@nameend,1024)
end
set @id = @id + 1;
end
select * from #tmp14;
---sample table
CREATE TABLE #Tmp13(
p_Num INTEGER NOT NULL PRIMARY KEY
REQUESTED varchar(50),
DATE1 VARCHAR(50),
BY1 VARCHAR(50),
DESCRIPT TEXT
);
INSERT INTO #tmp13 (p_Num, REQUESTED, DATE1, BY1, DESCRIPT)
VALUES (100, 'John', '5/30/2017', 'James', '05/30/2017 12:25am Eastern Standard Time - Mjames reported changes in the pages 05/30/2017 10:35AM JRachael agreed to work on the report and report to James 05/30/2017 10:00 AM James reports errors in page.',NULL);
INSERT INTO #tmp13(WO_NUM, Opendate, ClosedDate, Note)
VALUES (200, 'John', '6/1/2017', 'Rachael', '06/1/2017 3:20PM Eastern Standard Time - Rsubramaniam reported phone is not functional 06/1/2017 4:00PM Service took the phone and replaced it with new one');
OUTPUT:
Id product_num REQUESTED FirstDate RequestedBY date name date Name
1 100 John 5/30/2017 james 5/30/2017 mjames 5/30/2017 jRachael
