• Another possibility is to use a while statement and delete/insert a smaller amount of records on each iteration so that the optimizer will decide to use a lower level of locking like page locks. This way you can avoid dirty reads because select statements will wait, but only until the current batch is complete.

    While 0=0

    Begin

    Delete/Insert top (50000) TableAlias

    From TableName TableAlias

    Join etc

    Where etc

    If @@RowCount < 50000

    Break

    End

    One implication of this method is that each batch is it's own transaction, so the whole thing cannot be committed/rolled back. Additionally this will ultimately take longer than executing full delete/insert in one fell swoop, but the duration on any particular row or page being locked will be decreased.

    P.S. It probably goes without saying, but - you would need to change the 50000 to whatever makes sense.