Im working on a pos system based on sqlite. One table contains all orders/buys. Every night the system should copy all orders to an different table in a different database, because otherwise the orders table will become too heavy (monthly about 15000 new rows) and the whole system will become too slow.
My problem is, that when i copy the daily orders into the backup database, im loosing my last autoincrement value. That means: sqlite is starting again with 1. But i need a unique ID for the orders over 2 databases/tables?
Does somebody has an idea, how i can force sqlite to not reset the last autoincrement value, when deleting all rows?
CREATE TABLE backup_table(
tableflag integer not null,
autoincid integer not null,
column-n ....,
.....,
PRIMARY KEY (tableflag , autoincid)
);
and copy the original autoincrement value from the original table to your backup table and use a special flag for every table you want to unite
Otherwise you could insert a row with the highest autoincrement value plus 1 of your backup table after the deletion as first row, Then sqlite will just increment that value.