title: Mass Insert Into Sql Server
body: <p>I am attempting to insert a mass of records into SQL Server 2005 from Vb.Net.  Although the insertion is working fine, I am doing my best to try to make it as fast as possible.  Currently, it takes ~ 11 mins for 100,000 records.  What would be the suggested approach to inserting a large number of records into SQL Server from an Application?</p>
  
  <p>My current apporach is basically opening the connection, iterating through my list of information and firing off individual sql insert statments, and then closing the connection.  Anyone have a better suggestion on how to do this?</p>
  
  <p>Current Function:</p>
  
  <pre><code>Public Sub BatchInsert(ByVal ParamCollections As List(Of SqlParameter()))
  
      Dim Conn As SqlConnection = New SqlConnection(DBHelper.DatabaseConnection)
      Using scope As TransactionScope = New TransactionScope()
      Using Conn
  
          Dim cmd As SqlCommand = New SqlCommand("sproc_name", Conn)
  
          Conn.Open()
          cmd.CommandType = CommandType.StoredProcedure
  
          For i = 0 To ParamCollections.Count - 1
  
              cmd.Parameters.Clear()
              cmd.Parameters.AddRange(ParamCollections(i))
              cmd.ExecuteNonQuery()
  
          Next
  
          Conn.Close()
          scope.Complete()
      End Using
      End Using
  
  End Sub
  </code></pre>