title: Insert multiple rows from another table and get the IDs of inserted rows in SQL Server 2000
body: <p>I have table A and table B, table B has the same columns of A and only one extra column x , I want to insert some rows from A to B , and then I want to update each one of these last inserted rows with a new value for column x.</p>
  
  <p>How can I do it in SQL Server 2000?</p>
  
  <p>Now I am using this query in C# code, I select all wanted rows from A and I save it in <code>DataTable</code></p>
  
  <pre><code>string Query = "Select * from A where ID = 5";
  
  SqlDataAdapter dap = new SqlDataAdapter(Query, Conn);
  dap.Fill(DataTablle1);
  
  foreach (DataRow row in DataTablle1.Rows)
  {
        string query2 = "Insert into B (col a , col b, col x) values ('" + row["col a"] + "','" + row["col b"] + "',3)";
  }
  </code></pre>
  
  <p>This way requires many insert statement depending on the number of rows in table A, is there any way to achieve it with minimum number of insert statement?        </p>