Options actual staging table vs. Temp Table

Question

Options actual staging table vs. Temp Table

Luke L

SSC-Dedicated

Points: 35547
More actions
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]

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

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

Michael Tocik SSCertifiable Points: 7043 More actions · Answer 1

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.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

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.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

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]

Pam Brisjar SSChampion Points: 12094 More actions · Answer 4

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

kevin mann Hall of Fame Points: 3894 More actions · Answer 5

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!