Hans Hans - 2 years ago 143
SQL Question

Truncate followed by Insert in one transaction MySQL

I have a database with frequently changing raw data.
There is a pretty complex process that creates a summary table of this data.

I've created a Stored Procedure to create this table which uses a sequence of three tables. As I don't yet track what data has been updated (I'm working on that) I simply populate these queries with a sequence of:

TRUNCATE tablename ...
INSERT INTO tablename ...

I have moved all of these three queries into a single transaction:


[Sequence of TRUNCATE / INSERT queries]


I expected that after the commit I would immediately see the resulting table but there seems to be a 2-3 second period where the table is empty. Is there a simple way to fix this?

Answer Source

NO!! , you cant use TRUNCATE in a transaction. Truncate deletes the hole Table File an re-creates it . Truncate operations cause an implicit commit.

a idea:

It can be work with RENAME. Rename is a single transaction

CREATE TABLE table_copy like your_table;
INSERT DATA in table_copy;
RENAME your_table TO your_table_old, table_copy TO your_table;
DROP table_copy;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download