Oracle数据的批量插入

前两天接到一个需求——需要编程将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类来提高大量数据导入的性能。有需要的朋友可以查