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?
SourceTable(column1) Inserts to
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()
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
This allows me to specify the name of the source column and its destination.