Loop insertion

  • Anyone can help me to enhance this script for insert of data 1 by 1 from another table, this is the same data inside, same index, fields, etc. I want to insert 1 by 1 into new table, that not cause of blocking.

    begin transaction

    declare @3mthsAgo datetime

    set @3mthsAgo = convert(datetime, convert(varchar, getdate()-90, 112), 103)

    while exists (select * FROM dbo.Dummy_INT_HL7INMESGTRANS with (nolock))

    begin

    set rowcount 1

    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

    set rowcount 0

    end

    SET IDENTITY_INSERT dbo.Dummy_INT_HL7INMESGTRANS OFF

    commit transaction

    thank you

  • How many rows are you talking about copying? I'm thinking that you don't actually need the WHILE loop because of the explicit transaction you've declared. Use set based code based on INSERT/SELECT.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • only 100 records, I want to trace some blocking error while im deleting, at the same time insert 1 record at a time. this one is running but some problem, the records is not inserted into table... can you help me to enhance this code

    thanks

  • The WHILE condition doesn't make sense for the code you posted... it will run forever because something will always exist in the table.

    Also, you're trying to insert into the same table you're selecting form.

    Keep in mind that anything that changes data will cause blocking because the affected rows must lock to be changed. Most blocking is a healthy part of SQL Server transactions.

    Last but not least, you aren't using the 3 month variable at all to control your archive process.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply