Insert into select from taking hours (URGENT))

  • While consulting for clients with large databases, I have encountered this on several occasions. To get rid of the table lock you need to insert less rows at a time. so you need to perform a top 4000 query or a rowcount and then another and another. I tested this method about 3 years ago on a production database that could not be updated because of Table Locking.

    You may have to modufy the where clause to meet your criteria

    Then in the where clause make certain you say something like

    while exists(select ... from Whatever where id is not in (select . . . )

    begin

    set rowcount = 4000

    insert into Whatever (...)

    select . . . where

    where id is not in (select . . . )

    set rowcount = 0

    end

    Dr. Peter Lundberg MCSD

    peter lundberg


    peter lundberg

Viewing post 16 (of 16 total)

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