Some databases allow you to temporarily turn off auto-generating IDs so
that you can import a table keeping its original IDs intact. I wrote a
database abstraction layer a few years back for another project that
specifically allowed you to make an identical copy of a database from a
MySQL database to a PostgreSQL database on a different machine and
preserving the IDs was something I needed to figure out. Here is how you do
it with MSSQL, MySQL, and PostgreSQL. Maybe Massimo can add this as an
option to the DAL's import method:
First, import your records, then run these on your database engine:
MSSQL:
db.executesql('SET IDENTITY_INSERT dbo.mytable OFF');
db.executesql('DBCC CHECKIDENT (mytable)');
MySQL:
count = db.executesql('SELECT MAX(id) FROM mytable;')
count += 1
db.executesql('ALTER TABLE mytable AUTO_INCREMENT=%s;' % count)
PostgreSQL:
count = db.executesql('SELECT MAX(id) FROM mytable;')
count += 1
db.executesql('ALTER SEQUENCE mytable_sequence RESTART WITH %s;' % count)
For all 3 database engines, this sets the auto-increment counter to the max
ID it finds in the table (AFTER the import) +1 so that new rows will have
the proper IDs.
