Post reply

Options actual staging table vs. Temp Table

  • May 10, 2006 at 12:57 pm

    #68519

    I’m currently working on a data extract routine to some info out of my database and into someone else’s.  They gave me the file specs, btw it needs to be a tilde “~ “delimited File.  I’m looking for opinions on whether to query my db and dump the data into a temp table via a very long Execute SQL statement and then export it via a Data transformation task to my text file, or to use a permanent staging table before using the Transform task to move the data?  Unfortunately I can’t just do a straightforward SELECT for the source side of the DTS job or I would.  That’s why I need to have it in temp or staging table first.

     

    Thanks in advance for any insight you can provide.

     -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 11, 2006 at 5:04 am

    #637188

    Hi
     
    In my opinion it would be better to use a actual table apposed to a #Table. For the reason should there be any type of query regarding the accuracy of the data at least you would have your table to revert back to. But you may have a trade off with performance.

  • May 11, 2006 at 6:03 am

    #637201

    If you're not planning to keep the data, and I assume that you are not if you are considering a temp table, I would use the temporary table.  At the end, you simply drop the table instead of having to clean an existing one. 

  • May 11, 2006 at 6:08 am

    #637203

    Yeah I'm not really planning to keep the data.  I already have it in my db and I can recreate the data with the tables by rerunning portions of the job if things start to fail, which is why I was leaning that way.  cleaning an existing one wouldn't realy be all that hard, just a truncate statement before I begin populating it with records... I need to do some testing to see which one is faster, but in all acuality this is going to be a job that runs at 0 dark 30 so performance isn't my biggest chalenge. 

    Thanks for your input.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 11, 2006 at 8:16 am

    #637265

    I had a similar situation and used a table in the database.  I found it quite handy for troubleshooting at times.

  • May 11, 2006 at 12:29 pm

    #637366

    definately use a temp table and if space-permitting keep a log of transactions made from the exchange. I can't count how many times I had a particular customer complaim that the data result was wrong and I was able to go back (and not have to download the 3 gig file) and say to them, because you sent it to me this way.

    CYA is always more important than performance!

Viewing 6 posts - 1 through 5 (of 5 total)

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