title: How do I identify the error row when using batch insert (INSERT INTO table2 SELECT * FROM table1)
body: <p>I use the following SQL statement to batch insert large amount of data into another table, for example:</p>
  
  <pre><code>INSERT INTO table2 (col1, col2) 
      SELECT col1, col2 
      FROM table1 
      WHERE condition and some logics ...;
  </code></pre>
  
  <p>Normally, roughly 5000 rows are inserted into table2.</p>
  
  <p>However, if 2 rows are invalid in a batch, and they causes some an error when inserting data. </p>
  
  <p>SQL Server raises an error and stops (or rollback) the statement.</p>
  
  <p>And therefore, no row are inserted into table2 because of an error.</p>
  
  <p>My questions are:</p>
  
  <ol>
  <li><p>How do I insert all valid data as much as possible into a target table if an error occurs in a batch insertion ?</p></li>
  <li><p>In addition, how do I identify the rows which cause errors after SQL Server raises errors ?</p></li>
  </ol>
  
  <p>I searched the Internet for any possible solution, but I can't find any hit about it.</p>