How Transform OracleAdapter Dataset to SQL BulkCopy

dataset oracle oracle-manageddataaccess sqlbulkcopy vb.net

Question

I've had success using theOracle.ManagedDataAccess.Client to download data into a database from an Oracle serverDataset and utilizedSqlBulkCopy want to add thatDataset If the tables have identical column names in a SQL database (see below).

How would I approach influencing theInsert of theDataset in order for me to define where the columns in theDataset enabling me to utilize non-identical tables, go into the SQL table?

E.g. SourceTable(column1) includes inDestinationTable(column4)

I have the following idea on how a SQL Insert command may be used to do this:

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

But I have no idea how to control theDataset into my SQL query. Code thus far for me:

    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()
1
0
6/19/2017 12:40:56 PM

Accepted Answer

It seems that I may use theColumnMappings efficiency ofBulkCopy to fix my problem discovered from the post: Given a dataset, add data to a table.

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

I can now define the source column's name and its final destination thanks to this.

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

0
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