Truncate a SQLite table if it exists?

Solution 1:

It is the two step process:

  1. Delete all data from that table using:

    Delete from TableName
    
  2. Then:

    DELETE FROM SQLITE_SEQUENCE WHERE name='TableName';
    

Solution 2:

IMHO, it is more efficient to drop the table and re-create it. And yes, you can use "IF EXISTS" in this case.

Solution 3:

Just do delete. This is from the SQLite documentation:

The Truncate Optimization

"When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster. Prior to SQLite version 3.6.5, the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5."