SQL loop never stops even if I define loop number
-
Hi,
I have a temporary table, filled from an csv file, then this table values will be inserted in other table, so I am doing a loop of all temporary table rows and I insert/update other table, for the 4 rows it works perfectly,but my code didn't respect the number of rows and never stops, What should I do ?
this is the temporary table rows :
and my code:
drop table #myTempTable
Create table #myTempTable
(
id int,
CCode varchar(50),
GLAc varchar(100),
GLAm varchar(100)
)
go
--Isert from csv file to #myTempTable
BULK
INSERT #myTempTable
FROM 'C:\Users\...\BAtest1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
-- #myTempTable rows = 4 so @@rowcount = 4
--Declare variables
declare @sessionID int
set @sessionID = 123
declare @CompanyCode int
set @CompanyCode = 3606
declare @GLAccount int
set @GLAccount = 3607
declare @GLAmount int
set @GLAmount = 3610
declare @parentQuestion int
set @parentQuestion = 3602
declare @subSurveySessionID int
set @subSurveySessionID = 21
declare @userid int
set @userid = 144
declare @idTempTable int
set @idTempTable = 1
declare @answerId1 int
declare @answerId2 int
declare @answerId3 int
set @answerId1 = 535
set @answerId2 = @answerId1+1
set @answerId3 = @answerId1+2
while (@@rowcount > 0)
begin
-- insert new answers for subsurvey, I have 3 questions so I will have 3 answers rows to add by iteration (iteration = row number of #myTempTable)
-- in the first iteration answerId1=535, answerId2=536. answerId3=537
-- in the second iteration answerId1=538, answerId2=539. answerId3=540 same thing for third and fourth iteration
SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].Answers ON
insert into [iQMPlatform_PwC_Demo].[dbo].Answers
([id],[sessionId],[questionId],[value],[AnswerdOn],[userId],[Visible],[SubSurveySessionId]) values
(@answerId1, @sessionID, @CompanyCode, (select CCode from #myTempTable where id=@idTempTable), GETDATE(),@userID, null, @subSurveySessionID ) ,
(@answerId2, @sessionID, @GLAccount, (select GLAc from #myTempTable where id=@idTempTable), GETDATE(),@userID, null, @subSurveySessionID),
(@answerId3, @sessionID, @GLAmount,(select GLAm from #myTempTable where id=@idTempTable), GETDATE(),@userID, null, @subSurveySessionID)
update [iQMPlatform_PwC_Demo].[dbo].Answers set value=value+CONVERT(varchar(10), @subSurveySessionID) +',' where sessionId=123 and questionId=3602 and SubSurveySessionId is null
SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].Answers OFF--insert new subSurveySession row
SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions] ONinsert into [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions]
( [id],[lastQuestionId],[name],[questions],[answered],[mandatory] ,[mandatoryAnswered],[subSurveyId])values
(@subSurveySessionID, @GLAmount,(select CCode from #myTempTable where id=@idTempTable)+'#'+(select GLAc from #myTempTable where id=@idTempTable),3,3,0,0, @parentQuestion)
SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions] OFF
-- Then I increment the counter, example : after iteration 1 you will find new value for each row
set @subSurveySessionID+=1 --@subSurveySessionID+=1will be = 22,
set @idTempTable+=1 -- @idTempTable = 2 (row 2 of the #myTempTable)
set @answerId1 = @answerId3 --answerId1=538
set @answerId2 = @answerId1+1 --answerId2=539
set @answerId3 = @answerId1+2 --answerId3=540
endthanks for help
-
The reason your loop never stops is because @@rowcount is updated by the insert statements.
However, your whole code example is not a good way to write SQL code. There is no need to insert 'values' into the tables, and do the separate update statement, or have a while loop at all.
- alastair.beveridge - Wednesday, March 1, 2017 2:12 AM
I don't follow you, Why there is no need to insert values inti my tables ?
-
Assigning a value to a variable sets @@ROWCOUNT to 1, so @@ROWCOUNT will always be 1 at the end of the loop and hence you'll be looping indefinitely. I have to ask, though, why are you using a loop to do this at all? Surely you can do this with one INSERT, UPDATE or MERGE statement?
John
- John Mitchell-245523 - Wednesday, March 1, 2017 2:17 AM
Because I need to increment questionID = @answerId1, @answerId2, @answerId3 and @subSurveySessionID for each iteration,
I also tried While (Select Count(*) From #myTempTable) > 0 same problem loop infinitly -
You should be doing something along the line of
insert into answers
select appropriate fields
from #myTempTable(this is obviously a very simplistic example, you may need an unpivot statement or similar in there too).
rather than using the 'values' clause in the inserts.
- benkraiemchedlia - Wednesday, March 1, 2017 2:19 AM
Then use a numbers, or tally, table. Do you really need to increment, though? Your table has an identity property. Use it, instead of turning it off for the duration of the insert.
I also tried While (Select Count(*) From #myTempTable) > 0 same problem loop infinitly
Of course. You don't delete any of the rows from your temp table, so the count will always be greater than 0.John
-
Here's how to do it without loops. Comment and uncomment as needed.
I didn't know what to do with the update or it was actually needed.Create table #myTempTable
(
id int,
CCode varchar(50),
GLAc varchar(100),
GLAm varchar(100)
)go
----Isert from csv file to #myTempTable
--BULK
--INSERT #myTempTable
--FROM 'C:\Users\...\BAtest1.csv'
--WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
INSERT INTO #myTempTable
VALUES(1,'A', 'B', 'C'),
(2,'E', 'F', 'G'),
(3,'H', 'I', 'J'),
(4,'K', 'L', 'M')
GO-- #myTempTable rows = 4 so @@rowcount = 4
--Declare variables
declare @sessionID int = 123
declare @CompanyCode int = 3606
declare @GLAccount int = 3607
declare @GLAmount int = 3610
declare @parentQuestion int = 3602
declare @subSurveySessionID int = 21
declare @userid int = 144
declare @idTempTable int = 1
declare @answerId1 int = 535
declare @answerId2 int = @answerId1+1
declare @answerId3 int = @answerId1+2--SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].Answers ON
--insert into [iQMPlatform_PwC_Demo].[dbo].Answers
--(
-- [id],
-- [sessionId],
-- [questionId],
-- [value],
-- [AnswerdOn],
-- [userId],
-- [Visible],
-- [SubSurveySessionId]
--)
SELECT ((ROW_NUMBER() OVER(ORDER BY t.id)-1) / 3 * 3) + answerId + @answerId1,
@sessionID,
@CompanyCode,
x.Value,
GETDATE(),
@userid,
null,
@subSurveySessionID + (ROW_NUMBER() OVER(ORDER BY t.id)-1)
FROM #myTempTable t
CROSS APPLY (VALUES(0, CCode),
(1, GLAc),
(2, GLAm))x(answerId, Value)--update [iQMPlatform_PwC_Demo].[dbo].Answers set
-- value = value + CONVERT(varchar(10), @subSurveySessionID) + ','
--where sessionId = 123
--and questionId = 3602
--and SubSurveySessionId is null--SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].Answers OFF
----insert new subSurveySession row
--SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions] ON--insert into [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions]
--(
-- [id],
-- [lastQuestionId],
-- [name],
-- [questions],
-- [answered],
-- [mandatory] ,
-- [mandatoryAnswered],
-- [subSurveyId]
--)
select
@subSurveySessionID + (ROW_NUMBER() OVER(ORDER BY t.id)-1),
@GLAmount,
CCode + '#' + GLAc,
3,
3,
0,
0,
@parentQuestion
from #myTempTable t--SET IDENTITY_INSERT [iQMPlatform_PwC_Demo].[dbo].[SubSurveySessions] OFF
go
drop table #myTempTable
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply