title: Bulk insert from C# list into SQL Server into multiple tables with foreign key constraints
body: <p>I am completely clueless with this problem, Any help would be highly appreciated:</p>
  
  <p>I have two tables, one is the master data table (<code>Table A</code>), the other table (<code>Table B</code>) has a foreign key relationship with multiple entries (to be specific 18) for one entry in <code>Table A</code>.</p>
  
  <p>I am getting the data in a list and wish to insert it in SQL Server database.</p>
  
  <p>I am currently using the below pattern but is taking <b>14 minutes</b> for inserting 100 rows in <code>Table A</code> and corresponding 18*100 rows in <code>Table B</code>.</p>
  
  <pre><code>using (SqlConnection conn = new SqlConnection(conStr))
  {
      foreach (var ticket in Tickets)
      {
          sql = string.Format(@"INSERT INTO dbo.Tickets([ColumnA], [ColumnB] ,..." + @")
                                VALUES(@ColumnA, @ColumnB,@ColumnC, @ColumnD, .... +
                              @"SELECT SCOPE_IDENTITY();");
  
          using (cmd = new SqlCommand(sql, conn))
          {
              cmd.Parameters.AddWithValue("@ColumnA", (object)ticket.Id ?? DBNull.Value);
              cmd.Parameters.AddWithValue("@ColumnB", (object)ticket.Address ?? DBNull.Value);
              cmd.Parameters.AddWithValue("@ColumnC", (object)ticket.Status?? DBNull.Value);
              ....
  
              conn.Open();
              TableA_TicketId = Convert.ToInt32(cmd.ExecuteScalar());
          }
      }
  } 
  </code></pre>
  
  <p>I use <code>SCOPE_IDENTITY()</code> to get the latest identity from table A for each record inserted and use it for insertion in second table</p>
  
  <pre><code>sql = string.Format(@"INSERT INTO Tickets_Fields ([TableA_TicketId], [FieldName], [Key],[Value]) 
                        VALUES (@TableA_TicketId, @FieldName, @Key, @Value);");
  
  using (cmd = new SqlCommand(sql, conn))
  {
      foreach (var customField in ticket.CustomFields)
      {
          cmd.Parameters.Clear();
          cmd.Parameters.AddWithValue("@TableA_TicketId", (object)TicketId ?? DBNull.Value);
          cmd.Parameters.AddWithValue("@FieldName", (object)"CustomField" ?? DBNull.Value);
          ...
          cmd.ExecuteNonQuery();
      }
  }
  
  conn.Close();
  </code></pre>
  
  <p>Please suggest if I can improve the performance of this code by any means. Or is their any better/faster way of doing it?</p>