title: Using CAST with an insert into ... select
body: <p>I am trying to cast some bad data into proper data using <code>CAST(column AS DECIMAL(7,2))</code>, which works when just SELECTing, but once combined with an INSERT statement, the statement fails:</p>
  
  <pre><code>/* setup */
  drop table if exists dst;
  create table dst (
    id int not null auto_increment
    , columnA decimal(7,2)
    , primary key (id)
  );
  
  drop table if exists src;
  create table src (
    id int not null auto_increment
    , columnA varchar(255)
    , primary key (id)
  );
  
  insert into src (columnA) values ('');
  
  /* test */
  
  /* This works as I would like it to*/
  select CAST(columnA AS decimal(7,2)) from src; /* returns 0.00 */
  
  /* This fails */
  insert into dst (columnA)
  select CAST(columnA AS decimal(7,2)) from src; 
  /*0 19  21:01:56    insert into dst (columnA)
   select cast(columnA as decimal(7,2)) from src  Error Code: 1366. Incorrect decimal value: '' for column '' at row -1   0.000 sec*/
  </code></pre>
  
  <blockquote>
    <p>Edit: this is a pared down reproduction example - the data I am
    working with is much more varied than just an empty string - it might
    also be a non-numeric string value or a numeric value that is outside
    of the bounds of decimal(7,2) - in which the <code>CAST</code> statement works
    the way I would like it to when only in a <code>SELECT</code> but fails when
    trying to use them as part of the <code>INSERT</code>.</p>
  </blockquote>
  
  <p>Am I doing something wrong, or is there another way to achieve what I am looking for?</p>
  
  <p>I am using MYSQL 5.6.11 on WIN 7 x64</p>