August 18, 2008 at 3:32 am
anyone can help me to this query, everytime I run this query continuosly looping, but only 100 data that i insert, how can i stop looping after they reach 100 records
this is the query thanks
declare @count bigint
declare @msgid numeric(30,0)
begin transaction
set @count = 1
while @count > 0
begin
--set rowcount 1
select top 1 @msgid = INT_HL7INMESGTRANS_ID FROM dbo.Dummy_Data_INT_HL7INMESGTRANS with (nolock) where institution='BBKD' and UpdateStatus='N'
set IDENTITY_INSERT dbo.Dummy_INT_HL7INMESGTRANS ON
insert into dbo.Dummy_INT_HL7INMESGTRANS (msg_id,message,status,received_date,process_date,
institution,app_code,int_hl7inmesgtrans_id)
select msg_id,message,status,received_date,process_date,
institution,app_code,int_hl7inmesgtrans_id
FROM dbo.Dummy_Data_INT_HL7INMESGTRANS with (nolock) where institution='BBKD' and UpdateStatus='N' and INT_HL7INMESGTRANS_ID = @msgid
update dbo.Dummy_Data_INT_HL7INMESGTRANS set UpdateStatus='Y' where institution='BBKD' and INT_HL7INMESGTRANS_ID = @msgid
set @count = @@rowcount
--break
end
SET IDENTITY_INSERT dbo.Dummy_INT_HL7INMESGTRANS OFF
commit transaction
August 18, 2008 at 3:47 am
Hi,
You can check with another loop inside the main loop.
Cheers!
Sandy.
--
August 18, 2008 at 3:56 am
Hi Sandy
can you enhance my query.
because i dont know what can i create a query.
Thank you
August 18, 2008 at 4:11 am
See, I can give you hint how to make it, Just take a counter and increase by 1 inside the loop, I can say some times it may reach upto 100 or sometimes not, as per your rowcount. but if more than 100 then it will catch the counter value and make a return so specify GOTO for out from loop.
follow this:
Declare @Cnt Int
Set @Cnt = 1
While
(
before end increase @Cnt = @Cnt +1
If @Cnt = 100
goto
)
Please follow this and try it, you can do this, its easy..:)
Cheers!
Sandy.
--
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply