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 ?
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.