How Transform OracleAdapter Dataset to SQL BulkCopy

Question

I've successfully used the Oracle.ManagedDataAccess.Client to fetch data from an Oracle server into a Dataset, and used SqlBulkCopy to insert that Dataset into a SQL database where the tables have matching columns names (see below).

How would I go about manipulating the Insert of the Dataset so I can specify where the columns in the Dataset would go into the SQL table, allowing me to use non-identical tables?

E.g. SourceTable(column1) Inserts to DestinationTable(column4)

I was thinking this may be able to be achieved with a SQL Insert statement like:

INSERT INTO DestinationTable ([column4]) Values ([column1])

But I don't know how to manipulate the Dataset into my SQL statement. My code so far:

    Dim i As Double = 1
    Dim j As Double = 5000

    Dim Oconn As New OracleConnection(connectionString & mySource)

    Oconn.Open()

    Dim Osqlstr As String = "Select column1, " _
                                & "column2, " _
                                & "column3, " _
                                & "column4, " _
                                & "from " _
                                & "(Select rownum r, " _
                                & "column1, " _
                                & "column2, " _
                                & "column3, " _
                                & "column4, " _
                                & "from mysourcetable)" _
                                & "where rownum >=" & i & " and rownum <=" & j _
                                & " order by column1 asc"

    Dim Ocommand As New OracleCommand(Osqlstr, Oconn)
    Dim Oda As New OracleDataAdapter(Ocommand)
    Dim Ods As New DataSet()

    Oda.Fill(Ods)

    Using myBulk As New SqlBulkCopy(DB_COMMS)

        myBulk.DestinationTableName = "mydestinationtable"
        Try
            myBulk.WriteToServer(Ods.Tables(0))
        Catch ex As Exception
            MsgBox("Error:- " & ex.Message)
        End Try

    End Using

    Ods.Dispose()

    Oconn.Close()

Answer 1

It looks like I can use the ColumnMappings functionality of BulkCopy to solve my issue. Found by @AnandPhadke from post: insert data into table from a dataset

They linked this: https://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

This allows me to specify the name of the source column and its destination.

E.g. bulkCopy.ColumnMappings.Add("column1", "columnn4")