Post reply

Temp tables or local tables

  • October 13, 2016 at 4:34 pm

    #322109

    In my transform in my etl I have created permanent tables and prefixed them with "Temp" and i use these as stores to load data from the source system and then update my staging data. I know can create temptables in memory by setting the retain same connection and switch validation off but ive still to find a way that suites my needs using this. I dont like having lots of temp storage tables hanging about so im thinking of going down the path of creating permanent tables , populate them, update staging data using them and then delete them from schema. Just wondering what best practice would be?

  • October 13, 2016 at 6:09 pm

    #1906489

    ps_vbdev (10/13/2016)

    In my transform in my etl I have created permanent tables and prefixed them with "Temp" and i use these as stores to load data from the source system and then update my staging data. I know can create temptables in memory by setting the retain same connection and switch validation off but ive still to find a way that suites my needs using this. I dont like having lots of temp storage tables hanging about so im thinking of going down the path of creating permanent tables , populate them, update staging data using them and then delete them from schema. Just wondering what best practice would be?

    If you go the route of creating permanent working tables, consider making a "scratch" database that's set to the SIMPLE Recovery Model, NOT putting anything in that database that must persist for longer that it's being used so that you ever have to back it up, and making sure that the table names reflect which package they're being used for so as to avoid contention with other packages.

    And, don't forget to clean the litter box once in a while. I built a stored procedure in my "DBA" database that will rename any and every table that hasn't been used in a week (and it runs every day) or any other object that was created a week ago. This gives people a chance to recover from the mistake of trying to use scratch as a repository. The proc also automatically drops anything that has been renamed for a week.

    --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 2 posts - 1 through 1 (of 1 total)

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