Home Forums Article Discussions Article Discussions by Author Discuss content posted by Mel Sansone Conditional Set-Based Processing: Moving Towards a Best Practice for ETL RE: Conditional Set-Based Processing: Moving Towards a Best Practice for ETL

  • August 22, 2010 at 1:36 pm

    #1210145

    wardster (8/21/2010)

    Hi Jeff

    Thanks for the quick reply.

    The solution I'm in the middle of testing uses a permanent staging table rather than temp table or table variable. Does this mean I should be deleting from it outside the insert transaction? The staging table is brand new so I'm certain no other objects reference it apart from the stored proc with the delete in.

    First, unless you're trying to preserve the value of an IDENTITY column, I wouldn't use DELETE... I'd use TRUNCATE. It's a whole lot faster and easier on the LOG.

    Second, same thing applies. You'd have to do the DELETE or TRUNCATE outside the transaction or it may undo the DELETE or TRUNCATE if you rollback. Of course, that may be just what you want so the real answer is "It Depends". 😀

    --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)