Using insert into database.table1 select * from table2 where id=some_value;

Question

I want to copy a row from a table in a database to an identical table in another database. For testing purposes I created this:

CREATE TABLE stuff (absid integer primary key, otherfield string );

and table 'stuff' is as above in two database, testdb1 and testdb2. Then I put two rows into 'stuff' in testdb1. From a command line, I can then copy a row from one db to the other, thus:

prompt> sqlite3 testdb1

sqlite> attach database testdb2 as testdb2;

sqlite> insert into testdb2.stuff select * from stuff where absid=2;

sqlite> ^d

prompt>

So far so good. BUT: it is quite possible in the application where I actually want to do this for real, that there will be key clashes. For example, if I use the above sequence to copy the row back to testdb1, I get:

SQL error: PRIMARY KEY must be unique

What I would like to happen is that when the row is copied, a new unique absid is chosen automatically if there is a conflict. Is there a way I can specify this with a more complex "select * ..." above?

I guess I can get round this by creating another db (in memory, say) with the identical table but without the primary key constraint, and doing the copy in two steps (setting absid to null in between), but I'd prefer a smarter way if one exists.

Thanks,

Answer 1

You can specify the columns, omitting the primary key column, and the new row inserted will activate the automatic key generation. For example, assuming the primary key is in column col1, and columns col2 through col4 are non-key columns, try this:

sqlite> insert into testdb2.stuff (col2, col2, col4) 
  select col2, col3, col4 from stuff where absid=2;

Unfortunately, this means you can't use the "*" wildcard. There's no way in SQL to use a wildcard to mean "all columns except ones I specify."

Answer 2

In the end I made an in-memory database, with the same table except leaving off "primary key". I did a first "insert into mytable select * from ... " into the in-memory database, then set absid to null, and then a second "insert into ..." into the target db. That way I got the row copied and with a new key value.

Although there is a second step, this whole action (in my app) is not done with huge numbers of rows so efficiency becomes secondary to maintainability.