Vb.net SQL bulk insert/copy

Question

Hope someone could help,

Background: Got an old machine that puts data into an MS access file and we need to import the table into an SQL table, the access database keeps a rolling 3 days of data and then deletes the older data, the idea is to run a task every morning to insert the new records to sql, ignore existing so we can build a graph over the year.

I've extracted data from MS access into a Datatable called "table" and created the column names in an SQL table which matches, i've read about SQL Bulkcopy but all of the samples i've seen are in C# but any code converters error out and im not 100% that they'll do what im after.

Could anyone help out? Pete

Dim count As Integer = 0
    Dim table As DataTable = New DataTable
    Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data Source='C:\Machine.mdb';User Id=admin; Password=;")
    Dim sqlConnection As New SqlClient.SqlConnection("Data Source=10.75.24.94;Initial Catalog=CTData;User ID=sql;Password=")
    Try
        'Import the Access data
        accConnection.Open()
        Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Slot_Vision_Counters_table", accConnection)
        accDataAdapter.Fill(table)
        accConnection.Close()
        'Export to MS SQL
        For Each row As DataRow In table.Rows
            row.SetAdded()
            count = count + 1
        Next


Code for inserting the data to SQL?


    Catch ex As Exception
        If accConnection.State = ConnectionState.Open Then
            accConnection.Close()
        End If
        If sqlConnection.State = ConnectionState.Open Then
            sqlConnection.Close()
        End If
        MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
        & ex.ToString)
    End Try

Possible duplicate of [Mass Insert Into Sql Server](https://stackoverflow.com/questions/334919/mass-insert-into-sql-server) — Meta-Knight, Nov 27 '18 at 13:49
"all of the samples i've seen are in C# " - So you did not look at the documentation for the [SqlBulkCopy Class](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.7.2)? In the the top-right of that page if you see `>C#` then click on it and select `VB`. — TnTinMn, Nov 27 '18 at 14:31
I was hovering over that page i didnt realise you can change it to VB, my mistake - i wasnt quite sure if it ignores entries that would be the same tho? — Peter James, Nov 27 '18 at 15:46

score 0 · Answer 1 · answered Nov 27 '18 at 19:40

In SQL you need to create a Table variable, and then in VB.net create a DataTable variable (and define the columns) and first put the data in the DataTable variable.

Then pass the DataTable variable to either a SQL Query that SELECTS from the Table variable to INSERT into your Table, or create a Stored Procedure with a parameter that accepts the Table variable.

I can share some code later if you can't figure out how to do this.

Vb.net SQL bulk insert/copy

1 Answers1