Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table.

I wanted to make sure that if any statement in the transaction failed, the data will be untouched.

I can't have an empty or corrupted table if something fails.

This is how I planned to do it.

• BEGIN TRANSACTION

• TRUNCATE TABLE table_name

• INSERT INTO table_name

• COMMIT

I then learned that TRUNCATE TABLE table_name causes an implicit COMMIT, which makes the whole transcation pointless.

I then replaced the TRUNCATE TABLE table_name with a DELETE FROM table_name approach, which wasn't ideal - but that too causes an implicit COMMIT!

DROP TABLE table_name and CREATE TABLE table_name also causes an implicit COMMIT!

What method / logic is best to perform what I am trying to do?

Using MySQL 5.6.17 64-bit server running on Windows Server 2008 R2

Since TRUNCATE TABLE does an implicit commit, don't use it.

As long as the table does not have foreign keys, you can do it quickly like this:

CREATE TABLE mytable_new LIKE mytable; 
RENAME TABLE mytable TO mytable_old,mytable_new TO mytable;
INSERT INTO mytable ... ;

If the INSERT succeeds, then run this

DROP TABLE mytable_old;

If the INSERT rolls back, just revert back

RENAME TABLE mytable TO mytable_zap,mytable_old TO mytable;
DROP TABLE mytable_zap;

Give it a Try !!!

StackExchange Database Administrators Q#81958, answer score: 7