On Wed, Jul 11, 2012 at 2:32 PM, Mark Thornton
<mthornton@optrak.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