title: VB.Net SQL Server copy data from an on-the-fly-datatable into database
body: <p>This is doing my crust in... Working in VB.net 2010 and 2013. I have a datatable which is created on the fly and I want to deposit said data into a table in my SQL Server database in the fastest possible way. Currently the only thing I can get to work is "insert into..." for each row in the datatable by creating the sql to insert each value into each field. What I'd like is for SqlServer.datatable = onTheFlyDataTable.</p>
  
  <p>I've tried row by row, but I can't get that to work as the SQL I use for my server table is a "SELECT * FROM..." which is obviously read only.</p>
  
  <p>What I did get to run without errors, was something like this, but it never wrote the data back to the SQL Server Database. I did run without errors, and the dtTEST as the end of it <em>did</em> contain all the rows I'd added - it just didn't write it back to the database. Even tried .AcceptChanges...</p>
  
  <pre><code>Dim sSYSsqlTEST As String = "SELECT * FROM MyDataBaseTable;"
  Debug.Print(sSYSsqlTEST)
  
  Dim cmdTEST As SqlCommand = New SqlCommand(sSYSsqlTEST, cNN)
  Dim daTEST As SqlDataAdapter = New SqlDataAdapter(cmdTEST)
  Dim dsTEST As DataSet = New DataSet
  If Not IsNothing(daTEST) Then
      daTEST.Fill(dsTEST, "TEST")
  End If
  Dim dtTEST As DataTable = dsTEST.Tables("TEST")
  
  
  For each dr as datarow in myOnTheFlyDataTable
      Dim workRow As DataRow = workTable.NewRow ()
      workRow = dr
      workTable.Rows.Add (workRow)
  Next
  </code></pre>
  
  <p>Is what I'm trying to do achievable?</p>
  
  <p>Based on Pradeep's response, I've altered to this but I get "The given ColumnMapping does not match up with any column in the source or destination.
  "</p>
  
  <pre><code>Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(cNN)
  bulkCopy.DestinationTableName = "dbo." + sCUSTOMER + "_IMPORT"
  With bulkCopy.ColumnMappings
      For x As Integer = 0 To iNOfields
      Debug.Print("Column " + x.ToString + " Datatype = " + dtIMPORT.Columns(x).DataType.ToString)
      .Add(dtIMPORT.Columns(x).ColumnName, "[FIELD " + x.ToString + "]")
      Next
  End With
  Try
      ' Write from the source to the destination.
      bulkCopy.WriteToServer(dtIMPORT)
  Catch ex As Exception
      Debug.Print(ex.Message)
      Debug.Print("")
  End Try
  
  End Using
  </code></pre>