将数据从一个数据库字段复制到另一数据库字段 [英] Copying data from one database field to another database field
问题描述
我在这里有点发言。我们有一个用于oracle数据库的开发环境和一个sat环境。
这是我想要实现的目标。我想从开发人员的x列中复制数据。环境设置为与我的环境相同的x列数。我已经尝试了一些东西，并创建了几个函数，可以将所有记录复制到我的sat环境中（如果需要，可以从所有列或字段复制）。
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
如您所见，我需要在copydata函数中使用delete语句，因为否则我将收到违反唯一约束的错误。也没有唯一标识符，我可以使用更新查询创建相同的功能。
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.
我已经考虑过了，得出的结论是我应该执行以下操作：
1）在执行函数时首先创建一个从当前的当前环境复制（备份）。 （我们稍后将使用它）
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）执行复制所有数据时所用的相同操作，即删除卫星中的数据。
2) perform the same action used when copying all the data i.e. delete data in the sat.
3）以某种方式将我的开发环境中的数据与副本sat环境合并。
3) somehow merge the data from my development enviroment with the copy sat enviroment.
示例：
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
因此，您只能看到第2列和第4列包含开发环境中的数据，第1列和第3列仍包含数据在我以前的坐便环境中。
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.
编辑：这是我的进度。我决定创建一个临时表，其中包含来自SAT环境的信息。并决定将其用作备份。
我不仅将其用作备份，还使用了此表中需要的字段并创建了我的插入语句。
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.
推荐答案
如您所见，我需要在copydata函数中使用delete语句，因为否则我将收到违反唯一约束的错误。也不是唯一标识符。
"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?
无论是通过UPDATE还是通过从每个数据库中提取某些列的INSERT进行操作，您都需要将DEV中的行与SAT中的行进行匹配的一些方法。如果匹配不是唯一的，您将得到不确定的结果或比开始时更多的行。
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.
在下面的示例中说您想将第2列从DEV复制到SAT。决定DEV中的哪个值进入SAT中的哪一行？
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 ?
这篇关于将数据从一个数据库字段复制到另一数据库字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
