Re: DELETE vs TRUNCATE explanation

On Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton <mthornton(at)optrak(dot)com> wrote:

> On 11/07/12 21:18, Craig James wrote:
>
>>
>> It strikes me as a contrived case rather than a use case. What sort of
>> app repeatedly fills and truncates a small table thousands of times ...
>> other than a test app to see whether you can do it or not?
>>
> If I have a lot of data which updates/inserts an existing table but I
> don't know if a given record will be an update or an insert, then I write
> all the 'new' data to a temporary table and then use sql statements to
> achieve the updates and inserts on the existing table.
>
> Is there a better way of doing this in standard SQL?
>

If it's a single session, use a temporary table. It is faster to start
with (temp tables aren't logged), and it's automatically dropped at the end
of the session (or at the end of the transaction if that's what you
specified when you created it). This doesn't work if your insert/update
spans more than one session.

Another trick that works (depending on how big your tables are) is to scan
the primary key before you start, and build a hash table of the keys. That
instantly tells you whether each record should be an insert or update.

Craig

>
> Mark
>
>
>