Felix Schwarz wrote:
I have found the possibility of using
CREATE TABLE some_new_table ....
INSERT INTO some_new_table SELECT * FROM
some_old_table_with_same_layout;
to be extremely useful to recreate databases.
Since ROWID is a hidden field of every table: will sqlite copy over
the identical ROWID from some_old_table... to some_new_table? Or will
it create new ROWIDs for some_new_table?
(In this example some_new_table shall be an empty, freshly created
table.)
And is the answer to above question also true for INSERT-SELECT-pairs
across two attached database files?
Felix,
The answer depends on the type of your columns.
If you have an integer primary key column, then it is stored in the
rowid (i.e. the rowid isn't really hidden anymore). When you do the
INSERT...SELECT command it will insert new records with the same rowid
as the old table when it copies this column.
For other records it will assign new sequential rowids to the rows in
the new table as it copies the other columns from the old table. The
actual rowid shouldn't matter in this case because you should have your
tables linked by their primary keys (which won't change because they are
independent of the rowid).
The same thing applies across attached databases.
HTH
Dennis Cote
