Advantage of Temp Table

[shumaker]

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?

[Ray]

Not much difference between a temp table and permenant table. Security,
automatic distruction, and one or the other may faster depending on the
disk/file layout. I would ask why not just push it directly into the
destination? Also if the text is coming from another SQL available source,
why not skip both the text file and the temporary table?

It also may be faster depending on need for availability, space, and
indexing to create a new table and do select into based on a union all of
the two tables. Then rename the table.
<sh******@cs.fsu.edu> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?

[shumaker]

Thanks.

"I would ask why not just push it directly into the
destination?"

Because I have to do some logic based on what is not in the imported
data. If a certain record exists in my table, but not in the data
being imported, then I need to modify some flags in the existing
record. So I import into an empty table, and do updates "if not in"
imported table then update my existing table.

[--CELKO--]

>> What are the advantages of a temporary table as opposed to creating
a regular table and dropping it after use? <<

A regular table will port, can have constraints, DRI actions, etc. But
do not drop it after use; just clean it out before and after you load
the data for scrubbing.