<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="insert-select">
      </a>
      15.2.7.1 INSERT ... SELECT Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045182259824">
  </a>
  <a class="indexterm" name="idm46045182258784">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76747507"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">HIGH_PRIORITY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">IGNORE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    {   <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
      <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table_name</em></span> 
      <span class="token operator">|</span> <span class="token keyword">VALUES</span> <em class="replaceable">row_constructor_list</em>
    }
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> <em class="replaceable">assignment_list</em><span class="token punctuation">]</span>


<span class="token keyword"><em class="replaceable">value</em></span>:
    {<em class="replaceable">expr</em> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}

<em class="replaceable">value_list</em>:
    <span class="token keyword"><em class="replaceable">value</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">row_constructor_list</em>:
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>

<em class="replaceable">assignment</em>:
    <em class="replaceable">col_name</em> <span class="token operator">=</span> 
          <span class="token keyword"><em class="replaceable">value</em></span>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">row_alias</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_name</em>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">tbl_name</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_name</em>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">row_alias</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_alias</em>

<em class="replaceable">assignment_list</em>:
    <em class="replaceable">assignment</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">assignment</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   With
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
        SELECT
    </code>
   </a>
   , you can quickly insert many rows into a table
        from the result of a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement, which can select from one or many tables. For
        example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30096150"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tbl_temp2 <span class="token punctuation">(</span>fld_id<span class="token punctuation">)</span>
  <span class="token keyword">SELECT</span> tbl_temp1<span class="token punctuation">.</span>fld_order_id
  <span class="token keyword">FROM</span> tbl_temp1 <span class="token keyword">WHERE</span> tbl_temp1<span class="token punctuation">.</span>fld_order_id <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   statement in place of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   , as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39536678"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> ta <span class="token keyword">TABLE</span> tb<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    TABLE tb
   </code>
   is equivalent to
   <code class="literal">
    SELECT *
        FROM tb
   </code>
   . It can be useful when inserting all columns
        from the source table into the target table, and no filtering
        with WHERE is required. In addition, the rows from
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   can be ordered by one or
        more columns using
   <code class="literal">
    ORDER BY
   </code>
   , and the number
        of rows inserted can be limited using a
   <code class="literal">
    LIMIT
   </code>
   clause. For more information, see
   <a class="xref" href="table.html" title="15.2.16 TABLE Statement">
    Section 15.2.16, “TABLE Statement”
   </a>
   .
  </p>
  <p>
   The following conditions hold for
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
        SELECT
    </code>
   </a>
   statements, and, except where noted, for
   <code class="literal">
    INSERT ... TABLE
   </code>
   as well:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Specify
      <code class="literal">
       IGNORE
      </code>
      to ignore rows that would
            cause duplicate-key violations.
     </p>
    </li>
    <li class="listitem">
     <p>
      The target table of the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statement may appear
            in the
      <code class="literal">
       FROM
      </code>
      clause of the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      part of the query, or
            as the table named by
      <a class="link" href="table.html" title="15.2.16 TABLE Statement">
       <code class="literal">
        TABLE
       </code>
      </a>
      .
            However, you cannot insert into a table and select from the
            same table in a subquery.
     </p>
     <p>
      When selecting from and inserting into the same table, MySQL
            creates an internal temporary table to hold the rows from
            the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      and then inserts
            those rows into the target table. However, you cannot use
      <code class="literal">
       INSERT INTO t ... SELECT ... FROM t
      </code>
      when
      <code class="literal">
       t
      </code>
      is a
      <code class="literal">
       TEMPORARY
      </code>
      table, because
      <code class="literal">
       TEMPORARY
      </code>
      tables cannot be
            referred to twice in the same statement. For the same
            reason, you cannot use
      <code class="literal">
       INSERT INTO t ... TABLE
            t
      </code>
      when
      <code class="literal">
       t
      </code>
      is a temporary table.
            See
      <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
       Section 10.4.4, “Internal Temporary Table Use in MySQL”
      </a>
      , and
      <a class="xref" href="temporary-table-problems.html" title="B.3.6.2 TEMPORARY Table Problems">
       Section B.3.6.2, “TEMPORARY Table Problems”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTO_INCREMENT
      </code>
      columns work as usual.
     </p>
    </li>
    <li class="listitem">
     <p>
      To ensure that the binary log can be used to re-create the
            original tables, MySQL does not permit concurrent inserts
            for
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT
            ... SELECT
       </code>
      </a>
      or
      <code class="literal">
       INSERT ... TABLE
      </code>
      statements (see
      <a class="xref" href="concurrent-inserts.html" title="10.11.3 Concurrent Inserts">
       Section 10.11.3, “Concurrent Inserts”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      To avoid ambiguous column reference problems when the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      and the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      refer to the same
            table, provide a unique alias for each table used in the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      part, and qualify
            column names in that part with the appropriate alias.
     </p>
     <p>
      The
      <a class="link" href="table.html" title="15.2.16 TABLE Statement">
       <code class="literal">
        TABLE
       </code>
      </a>
      statement does not
            support aliases.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can explicitly select which partitions or subpartitions (or
        both) of the source or target table (or both) are to be used
        with a
   <code class="literal">
    PARTITION
   </code>
   clause following the name of
        the table. When
   <code class="literal">
    PARTITION
   </code>
   is used with the
        name of the source table in the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   portion of the statement,
        rows are selected only from the partitions or subpartitions
        named in its partition list. When
   <code class="literal">
    PARTITION
   </code>
   is used with the name of the target table for the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   portion of the statement,
        it must be possible to insert all rows selected into the
        partitions or subpartitions named in the partition list
        following the option. Otherwise, the
   <code class="literal">
    INSERT ...
        SELECT
   </code>
   statement fails. For more information and
        examples, see
   <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
    Section 26.5, “Partition Selection”
   </a>
   .
  </p>
  <p>
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   does not support a
   <code class="literal">
    PARTITION
   </code>
   clause.
  </p>
  <p>
   For
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT
        ... SELECT
    </code>
   </a>
   statements, see
   <a class="xref" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”
   </a>
   for conditions under which
        the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   columns can be
        referred to in an
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause. This also works for
   <code class="literal">
    INSERT ... TABLE
   </code>
   .
  </p>
  <p>
   The order in which a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   or
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   statement with no
   <code class="literal">
    ORDER BY
   </code>
   clause returns rows is
        nondeterministic. This means that, when using replication, there
        is no guarantee that such a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   returns rows in the same
        order on the source and the replica, which can lead to
        inconsistencies between them. To prevent this from occurring,
        always write
   <code class="literal">
    INSERT ... SELECT
   </code>
   or
   <code class="literal">
    INSERT ... TABLE
   </code>
   statements that are to be
        replicated using an
   <code class="literal">
    ORDER BY
   </code>
   clause that
        produces the same row order on the source and the replica. See
        also
   <a class="xref" href="replication-features-limit.html" title="19.5.1.18 Replication and LIMIT">
    Section 19.5.1.18, “Replication and LIMIT”
   </a>
   .
  </p>
  <p>
   Due to this issue,
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ...
        SELECT ON DUPLICATE KEY UPDATE
    </code>
   </a>
   and
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT IGNORE ...
        SELECT
    </code>
   </a>
   statements are flagged as unsafe for
        statement-based replication. Such statements produce a warning
        in the error log when using statement-based mode and are written
        to the binary log using the row-based format when using
   <code class="literal">
    MIXED
   </code>
   mode. (Bug #11758262, Bug #50439)
  </p>
  <p>
   See also
   <a class="xref" href="replication-sbr-rbr.html" title="19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication">
    Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based
        Replication”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
