Copying data from one database field  to another database field 
I am a bit in a pitch here. We have got a development environment and a sat environment for our oracle databases.  
Here is what I want to achieve. I want to copy data from an x number of columns of my dev. environment to the same x number of columns of my sat enviroment.  I've already tried some stuff and have created a couple of functions that copy all records to my sat environment( meaning from all the columns or fields if you will ).

    Function fncCopyDataTo(ByVal objFrom As OleDb.OleDbConnection, _
        ByVal objTo As OleDb.OleDbConnection, _
        ByVal objTrans As OleDb.OleDbTransaction, _
        ByVal strTable As String, _
        ByVal strFieldNames() As String, _
        ByVal strWhereClause As String, _
        ByVal blnBackup As Boolean, _
        ByRef strErrorText As String) As Boolean
            Dim objCmd As OleDb.OleDbCommand
            Dim drTemp As OleDb.OleDbDataReader
            Dim intAffected As Integer
            Dim strSQL As String
            Dim blnDeleteTo As Boolean
            Dim blnOK As Boolean
    
    
            blnOK = True
            strSQL = "SELECT * FROM " & strTable
            If Not strWhereClause Is Nothing Then
                strSQL = strSQL & " WHERE " & strWhereClause
            End If
    
            If Not blnBackup Then
                objCmd = objTo.CreateCommand()
                objCmd.Transaction = objTrans
                objCmd.CommandType = CommandType.Text
                objCmd.CommandText = strSQL
                drTemp = objCmd.ExecuteReader(CommandBehavior.Default)
    
                If drTemp.HasRows Then
                    blnDeleteTo = True
                Else
                    blnDeleteTo = False
                End If
                drTemp.Close()
    
                If blnDeleteTo Then
                    strSQL = "DELETE FROM " & strTable
                    If Not strWhereClause Is Nothing Then
                        strSQL = strSQL & " WHERE " & strWhereClause
                    End If
                    objCmd.CommandText = strSQL
                    intAffected = objCmd.ExecuteNonQuery()
                    If intAffected < 1 Then
                        blnOK = False
                        strErrorText = "Failed to delete table '" & strTable _
                        & "' because of issue with: intAffected = " & intAffected
                    End If
                End If
    
            End If
    
            If blnOK Then
                objCmd = objFrom.CreateCommand()
                objCmd.CommandText = "SELECT COUNT(*) FROM " & strTable
                If Not strWhereClause Is Nothing Then
                    strSQL = strSQL & " WHERE " & strWhereClause
                End If
                pbrVTReleaser.Maximum = objCmd.ExecuteScalar()
                pbrVTReleaser.Minimum = 0
                pbrVTReleaser.Value = 0
                objCmd.CommandType = CommandType.Text
                strSQL = "SELECT * FROM " & strTable
                If Not strWhereClause Is Nothing Then
                    strSQL = strSQL & " WHERE " & strWhereClause
                End If
    
                objCmd.CommandText = strSQL
    
                drTemp = objCmd.ExecuteReader(CommandBehavior.SequentialAccess)
                While drTemp.Read() And blnOK
                    StsBar.Text = "Releasing " & strTable
                    blnOK = fncCopyRowTo(strTable, Nothing, objTo, objTrans, drTemp, blnBackup, strErrorText)
                    pbrVTReleaser.Value = pbrVTReleaser.Value + 1
    
                End While
    
                strSQL = "SELECT * FROM " & strTable
    
                If Not blnBackup Then
                    objCmd = objTo.CreateCommand()
                    objCmd.Transaction = objTrans
                    objCmd.CommandType = CommandType.Text
                    objCmd.CommandText = strSQL
                    intAffected = objCmd.ExecuteNonQuery()
                End If
              
            End If
            drTemp.Close()
            fncCopyDataTo = blnOK
            objCmd.Dispose()
        End Function 


This is my copy data function and this is how I insert the rows 


  

    Function fncCopyRowTo(ByVal strTable As String, _
        ByVal strFieldName As String, _
        ByVal objTo As OleDb.OleDbConnection, _
        ByVal objTrans As OleDb.OleDbTransaction, _
        ByVal drFrom As OleDb.OleDbDataReader, _
        ByVal blnBackup As Boolean, _
        ByRef strErrorText As String) As Boolean
            Dim objCmd As OleDb.OleDbCommand
            Dim strSQL As String, strSQLCreateTB As String
            Dim strParam As String
            Dim intIndex As Integer
            Dim intAffected As Integer
            Dim blnOK As Boolean
            Dim strField As String
    
    
            blnOK = True
            objCmd = objTo.CreateCommand()
            objCmd.Transaction = objTrans
            objCmd.CommandType = CommandType.Text
    
            strSQL = "INSERT INTO " & strTable & " ( "
            strParam = ""
            objCmd.Parameters.Clear()
    
            strSQLCreateTB = "CREATE TABLE " & strTable & " ( "    
            For intIndex = 0 To drFrom.FieldCount - 1
                If intIndex > 0 Then
                    strSQL = strSQL & ", "
                    strSQLCreateTB = strSQLCreateTB & " char(10)"
                    strSQLCreateTB = strSQLCreateTB & ", "
                    strParam = strParam & ", "
                End If
                strField = drFrom.GetName(intIndex)
                strSQL = strSQL & strField
                strSQLCreateTB = strSQLCreateTB & strField
                objCmd.Parameters.Add(New OleDb.OleDbParameter(drFrom.GetName(intIndex), drFrom.GetValue(intIndex)))
                   
                strParam = strParam & "?"
            Next
            strSQL = strSQL & " ) VALUES ( " & strParam & " )" '
            strSQLCreateTB = strSQLCreateTB & " char(10))"
    
            If blnBackup Then
                blnOK = fncCreateTblDB(strSQLCreateTB)
            End If
    
            objCmd.CommandText = strSQL
    
            If Not blnBackup Then
                intAffected = objCmd.ExecuteNonQuery()
                If intAffected < 1 Then
                    blnOK = False
                    strErrorText = "Failed to insert into table '" & strTable _
                    & "' because of issue with: intAffected = " & intAffected
                End If
            End If
    
            fncCopyRowTo = blnOK
        End Function

As you can see I needed delete statement in my copydata function because otherwise I will get the unique constraint violated error. There are also no unique identifiers else I could have create the same function with an update query. 

I've thought about it , and I've come to the conclusion that I should do the following:
1) when executing the function  first create a copy( backup) from my current sat enviroment. ( we will use this later on) 

2) perform the same action used when copying all the data   i.e. delete data in the sat.

3) somehow merge the data from my development enviroment with the copy sat enviroment. 

example:
let's say 

    DEV data  has 4 columns   1   2   3   4 
                              x   x   x   x
                              x   x   x   x
                              x   x   x   x
    
    
    SAT data                  1  2  3  4     identical with different data  
                              0  0  0  0
                              0  0  0  0
                              0  0  0  0
    
    SAT COPY data             1  2  3  4 
                              0  0  0  0
                              0  0  0  0 
                              0  0  0  0
    
    new SAT DATA(merge)       1  2  3  4
                              0  x  0  x 
                              0  x  0  x
                              0  x  0  x

So ,as you can see only columns 2 and 4 contain the data from my dev enviroment and 1 and 3 still contain the data from my old sat enviroment.

This is what I want to achieve. 
Is this possible ? 
If so , any ideas how I can achieve this ? 





Copying data from one database field  to another database field 
===

I am a bit in a pitch here. We have got a development environment and a sat environment for our oracle databases.  
Here is what I want to achieve. I want to copy data from an x number of columns of my dev. environment to the same x number of columns of my sat enviroment.  I've already tried some stuff and have created a couple of functions that copy all records to my sat environment( meaning from all the columns or fields if you will ).

    Function fncCopyDataTo(ByVal objFrom As OleDb.OleDbConnection, _
        ByVal objTo As OleDb.OleDbConnection, _
        ByVal objTrans As OleDb.OleDbTransaction, _
        ByVal strTable As String, _
        ByVal strFieldNames() As String, _
        ByVal strWhereClause As String, _
        ByVal blnBackup As Boolean, _
        ByRef strErrorText As String) As Boolean
            Dim objCmd As OleDb.OleDbCommand
            Dim drTemp As OleDb.OleDbDataReader
            Dim intAffected As Integer
            Dim strSQL As String
            Dim blnDeleteTo As Boolean
            Dim blnOK As Boolean
    
    
            blnOK = True
            strSQL = "SELECT * FROM " & strTable
            If Not strWhereClause Is Nothing Then
                strSQL = strSQL & " WHERE " & strWhereClause
            End If
    
            If Not blnBackup Then
                objCmd = objTo.CreateCommand()
                objCmd.Transaction = objTrans
                objCmd.CommandType = CommandType.Text
                objCmd.CommandText = strSQL
                drTemp = objCmd.ExecuteReader(CommandBehavior.Default)
    
                If drTemp.HasRows Then
                    blnDeleteTo = True
                Else
                    blnDeleteTo = False
                End If
                drTemp.Close()
    
                If blnDeleteTo Then
                    strSQL = "DELETE FROM " & strTable
                    If Not strWhereClause Is Nothing Then
                        strSQL = strSQL & " WHERE " & strWhereClause
                    End If
                    objCmd.CommandText = strSQL
                    intAffected = objCmd.ExecuteNonQuery()
                    If intAffected < 1 Then
                        blnOK = False
                        strErrorText = "Failed to delete table '" & strTable _
                        & "' because of issue with: intAffected = " & intAffected
                    End If
                End If
    
            End If
    
            If blnOK Then
                objCmd = objFrom.CreateCommand()
                objCmd.CommandText = "SELECT COUNT(*) FROM " & strTable
                If Not strWhereClause Is Nothing Then
                    strSQL = strSQL & " WHERE " & strWhereClause
                End If
                pbrVTReleaser.Maximum = objCmd.ExecuteScalar()
                pbrVTReleaser.Minimum = 0
                pbrVTReleaser.Value = 0
                objCmd.CommandType = CommandType.Text
                strSQL = "SELECT * FROM " & strTable
                If Not strWhereClause Is Nothing Then
                    strSQL = strSQL & " WHERE " & strWhereClause
                End If
    
                objCmd.CommandText = strSQL
    
                drTemp = objCmd.ExecuteReader(CommandBehavior.SequentialAccess)
                While drTemp.Read() And blnOK
                    StsBar.Text = "Releasing " & strTable
                    blnOK = fncCopyRowTo(strTable, Nothing, objTo, objTrans, drTemp, blnBackup, strErrorText)
                    pbrVTReleaser.Value = pbrVTReleaser.Value + 1
    
                End While
    
                strSQL = "SELECT * FROM " & strTable
    
                If Not blnBackup Then
                    objCmd = objTo.CreateCommand()
                    objCmd.Transaction = objTrans
                    objCmd.CommandType = CommandType.Text
                    objCmd.CommandText = strSQL
                    intAffected = objCmd.ExecuteNonQuery()
                End If
              
            End If
            drTemp.Close()
            fncCopyDataTo = blnOK
            objCmd.Dispose()
        End Function 


This is my copy data function and this is how I insert the rows 


  

    Function fncCopyRowTo(ByVal strTable As String, _
        ByVal strFieldName As String, _
        ByVal objTo As OleDb.OleDbConnection, _
        ByVal objTrans As OleDb.OleDbTransaction, _
        ByVal drFrom As OleDb.OleDbDataReader, _
        ByVal blnBackup As Boolean, _
        ByRef strErrorText As String) As Boolean
            Dim objCmd As OleDb.OleDbCommand
            Dim strSQL As String, strSQLCreateTB As String
            Dim strParam As String
            Dim intIndex As Integer
            Dim intAffected As Integer
            Dim blnOK As Boolean
            Dim strField As String
    
    
            blnOK = True
            objCmd = objTo.CreateCommand()
            objCmd.Transaction = objTrans
            objCmd.CommandType = CommandType.Text
    
            strSQL = "INSERT INTO " & strTable & " ( "
            strParam = ""
            objCmd.Parameters.Clear()
    
            strSQLCreateTB = "CREATE TABLE " & strTable & " ( "    
            For intIndex = 0 To drFrom.FieldCount - 1
                If intIndex > 0 Then
                    strSQL = strSQL & ", "
                    strSQLCreateTB = strSQLCreateTB & " char(10)"
                    strSQLCreateTB = strSQLCreateTB & ", "
                    strParam = strParam & ", "
                End If
                strField = drFrom.GetName(intIndex)
                strSQL = strSQL & strField
                strSQLCreateTB = strSQLCreateTB & strField
                objCmd.Parameters.Add(New OleDb.OleDbParameter(drFrom.GetName(intIndex), drFrom.GetValue(intIndex)))
                   
                strParam = strParam & "?"
            Next
            strSQL = strSQL & " ) VALUES ( " & strParam & " )" '
            strSQLCreateTB = strSQLCreateTB & " char(10))"
    
            If blnBackup Then
                blnOK = fncCreateTblDB(strSQLCreateTB)
            End If
    
            objCmd.CommandText = strSQL
    
            If Not blnBackup Then
                intAffected = objCmd.ExecuteNonQuery()
                If intAffected < 1 Then
                    blnOK = False
                    strErrorText = "Failed to insert into table '" & strTable _
                    & "' because of issue with: intAffected = " & intAffected
                End If
            End If
    
            fncCopyRowTo = blnOK
        End Function

As you can see I needed delete statement in my copydata function because otherwise I will get the unique constraint violated error. There are also no unique identifiers else I could have create the same function with an update query. 

I've thought about it , and I've come to the conclusion that I should do the following:
1) when executing the function  first create a copy( backup) from my current sat enviroment. ( we will use this later on) 

2) perform the same action used when copying all the data   i.e. delete data in the sat.

3) somehow merge the data from my development enviroment with the copy sat enviroment. 

example:
let's say 

    DEV data  has 4 columns   1   2   3   4 
                              x   x   x   x
                              x   x   x   x
                              x   x   x   x
    
    
    SAT data                  1  2  3  4     identical with different data  
                              0  0  0  0
                              0  0  0  0
                              0  0  0  0
    
    SAT COPY data             1  2  3  4 
                              0  0  0  0
                              0  0  0  0 
                              0  0  0  0
    
    new SAT DATA(merge)       1  2  3  4
                              0  x  0  x 
                              0  x  0  x
                              0  x  0  x

So ,as you can see only columns 2 and 4 contain the data from my dev enviroment and 1 and 3 still contain the data from my old sat enviroment.

This is what I want to achieve. 
Is this possible ? 
If so , any ideas how I can achieve this ? 




