How Transform OracleAdapter Dataset to SQL BulkCopy

dataset oracle oracle-manageddataaccess sqlbulkcopy


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)


    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()


    Using myBulk As New SqlBulkCopy(DB_COMMS)

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

    End Using


6/19/2017 12:40:56 PM

Accepted Answer

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:

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

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

6/19/2017 9:34:13 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow