title: How to INSERT INTO...SELECT with ON DUPLICATE KEY
body: <p>I have two tables with identical structure. Table A contains all the current ads, Table B contains the archived ads. Column 1 (ad_id) is Primary Key, AI, INT. Table engine is MyISAM.</p>
  
  <p>I need to copy all the table A ads preceding a certain date to the  archive, table B. My goal is that all fields except ad_id are duplicated, ad_id should get auto-incremented. Here is what I have attempted:</p>
  
  <pre><code>INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
      SELECT *
      FROM A
      WHERE YEAR( ad_expire ) &lt;= 2012
  </code></pre>
  
  <p>Table B has many thousands of ads, Table A gets flushed often enough that the unique id field has low numbers that often duplicate the id's in Table B. So MySQL chucks a wobbly and tells me I have a <code>Duplicate entry '8577' for key 'PRIMARY'</code>.</p>
  
  <p>So I made several attempts to get past that:</p>
  
  <p>First I tried selecting the individual columns to insert, setting <code>ad_id</code> to NULL:</p>
  
  <pre><code>INSERT INTO B(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
      SELECT (NULL, `ad_advertiser`, `ad_ln`, `ad_expire`)
      FROM A
      WHERE YEAR( ad_expire ) &lt;= 2012 
  </code></pre>
  
  <p>That results in the error <code>#1241 - Operand should contain 1 column(s)</code>, which goes away if I use the wildcard * selector, but then I get the duplicate error.</p>
  
  <p>Next I tried <code>SELECT LAST_INSERT_ID()</code>, which always returns 0.</p>
  
  <p>Then I tried a few using <code>ON DUPLICATE KEY UPDATE</code>, but I can't seem to get that to work.</p>
  
  <p>I even tried to grab the highest id with:</p>
  
  <pre><code>SELECT @max := max(ad_id) FROM B;
  
  INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) &lt;= 2012
  
  ON DUPLICATE KEY UPDATE ad_id = @max + 1
  </code></pre>
  
  <p>This works for exactly one row, then results in a duplicate entry again (since @max is a static variable).</p>
  
  <p>What am I doing wrong here? Am I making this way too difficult?</p>