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开发者_如何学Python 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 ?

edit: Just gave some more thought to it, and perhaps I can create an insert statement with data from both my databases.

Edit : Here is my progress. I decided to create a temporary table containing the information from my SAT environment. And decided to use that as a backup.

Not only do I use this as a backup I also use the fields I need from this table and create my insert statement.

"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"

A unique constraint implies that there is some unique identifier. Do you mean that there is no single column that acts as an identifier?

Whether you do it by UPDATE or by an INSERT that pulls some columns from each database, you need some method of matching rows in DEV to rows in SAT. If the match is not unique, you will get either nondeterministic results or more rows than you started with.

The only alternative that I can think of is that you have some implicit ordering of the rows and you want to match on that -- which is ultimately no different, you could just generate the unique identifier from the orderings.

Say in the following example you wanted to copy column 2 from DEV to SAT. What decides which value in DEV goes into which row in SAT?

DEV data                  1   2
                          x   u
                          y   v
                          z   w


SAT data                  1  2
                          a  0
                          b  0
                          c  0

new SAT DATA(merge)       1  2
                          a  ?
                          b  ?
                          c  ?