前两天接到一个需求——需要编程将 SQL Server 中的数据插入至 Oracle 。数据大约有 20 多万条记录。开始的时候我采取了直接构建 SQL 插入的方式，结果耗时太长。为了提高性能我上网找了资料。最终采用 DataAdapter 批量插入至 Oracle ，提高了性能。

代码如下：

一，直接构建 SQL 语句插入

VB.NET

 1          sw.Start()

 2          ''''Read Z02J from SQL Server

 3          Dim sqlCmd As New SqlCommand()

 4          sqlCmd.Connection = sqlConnection

 5          sqlCmd.CommandText = "SELECT * FROM   Z02J"

 6

 7          Dim sqlDr As SqlDataReader

 8          sqlDr = sqlCmd.ExecuteReader()

 9

10          Dim cmdInsertZ02J As New OracleCommand()

11          cmdInsertZ02J.Connection = oraConnection

12          cmdInsertZ02J.CommandText = BuildSQLStatement(SQLType.Insert,"z02j")

13

14          Dim plantLever, material, oldMaterialNum, materialDescription As Object

15          While sqlDr.Read()

16              plantLever = ReadSqlDataReader(sqlDr, 0, "")

17              material = ReadSqlDataReader(sqlDr, 1, "")

18              oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")

19              materialDescription = ReadSqlDataReader(sqlDr, 3, "")

20              ''Insert to Oracle table Z02J

21              cmdInsertZ02J.Parameters.AddWithValue(":plantLever", plantLever)

22              cmdInsertZ02J.Parameters.AddWithValue(":material", material)

23              cmdInsertZ02J.Parameters.AddWithValue(":oldMaterialNum", oldMaterialNum)

24              cmdInsertZ02J.Parameters.AddWithValue(":materialDescription", materialDescription)

25              cmdInsertZ02J.ExecuteNonQuery()