前两天接到一个需求——需要编程将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()
26 End While
27 sw.Stop()
28 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())
二，采用DataAdapter实现批量插入
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 dtSqlZ02J As New DataTable
15 dtSqlZ02J.Columns.Add("plantLever")
16 dtSqlZ02J.Columns.Add("material")
17 dtSqlZ02J.Columns.Add("oldMaterialNum")
18 dtSqlZ02J.Columns.Add("materialDescription")
19
20 Dim plantLever, material, oldMaterialNum, materialDescription As Object
21 While sqlDr.Read()
22 plantLever = ReadSqlDataReader(sqlDr, 0, "")
23 material = ReadSqlDataReader(sqlDr, 1, "")
24 oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")
25 materialDescription = ReadSqlDataReader(sqlDr, 3, "")
26 dtSqlZ02J.Rows.Add(plantLever, material, oldMaterialNum, materialDescription)
27 End While
28 sw.Stop()
29 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())
30
31 sw.Start()
32 Dim oraDa As New OracleDataAdapter()
33 oraDa.InsertCommand = cmdInsertZ02J
34 oraDa.InsertCommand.Parameters.Add(":plantLever", OracleType.Char, 255, "plantLever")
35 oraDa.InsertCommand.Parameters.Add(":material", OracleType.Char, 255, "material")
36 oraDa.InsertCommand.Parameters.Add(":oldMaterialNum", OracleType.Char, 255, "oldMaterialNum")
37 oraDa.InsertCommand.Parameters.Add(":materialDescription", OracleType.Char, 500, "materialDescription")
38
39 oraDa.InsertCommand.UpdatedRowSource = UpdateRowSource.None
40 oraDa.UpdateBatchSize = 20 '''Adjust the batch size based on testing result
41
42 oraDa.Update(dtSqlZ02J)
43 sw.Stop()
44 Loger.Info("Insert to oracle used", sw.Elapsed.TotalSeconds.ToString())
在我的环境中批量插入24万笔记录用时大约260s左右。
貌似SQL Server中。net驱动程序提供了SqlBulkCopy类来提高大量数据导入的性能。有需要的朋友可以查下MSDN.