title: Using insert into database.table1 select * from table2 where id=some_value;
body: <p>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:</p>
  
  <pre><code>CREATE TABLE stuff  (absid integer primary key, otherfield string );
  </code></pre>
  
  <p>and table <code>'stuff'</code> is as above in two database, <code>testdb1</code> and <code>testdb2</code>. Then I put two rows into <code>'stuff'</code> in <code>testdb1</code>. From a command line, I can then copy a row from one db to the other, thus:</p>
  
  <pre><code>prompt&gt; sqlite3 testdb1
  
  sqlite&gt; attach database testdb2 as testdb2;
  
  sqlite&gt; insert into testdb2.stuff select * from stuff where absid=2;
  
  sqlite&gt; ^d
  
  prompt&gt;
  </code></pre>
  
  <p>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 <code>testdb1</code>, I get:</p>
  
  <pre><code>SQL error: PRIMARY KEY must be unique
  </code></pre>
  
  <p>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 <code>select * ...</code> above?</p>
  
  <p>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.</p>