Insert Query Looping rowcount

  • 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

  • Hi,

    You can check with another loop inside the main loop.

    Cheers!

    Sandy.

    --

  • Hi Sandy

    can you enhance my query.

    because i dont know what can i create a query.

    Thank you

  • 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