Oracle 批量插入數(shù)據(jù)怎么做

oracle批量插入數(shù)據(jù)使用sqlldr。

首先將要導(dǎo)入的excel文件另存為csv格式(默認(rèn)逗號間隔，另存為csv，不只是改后綴名)

然后用sqlldr導(dǎo)入。

sqlldr的參數(shù)很多，簡單的幾個就可以了：

建一個控制文件control.txt:

load data

infile 'f:\test.csv'

into table test1

(id char terminated by ',',

name char terminated by ',',

height char terminated by ',',

birth char terminated by whitespace)

----這里根據(jù)需要，修改數(shù)據(jù)文件的名字和路徑。括號里面的字段名和字段數(shù)也根據(jù)實際情況修改。由于保存的是csv，字段間以逗號隔開，因此控制文件里定義最后一個字段以whitespace來分隔，其它的用','

然后使用sql*loader:

sqlldr [email protected] control=f:\control.txt log=f:\test.log bad=f:\testbad.csv

userid后跟用戶名密碼，@后的是網(wǎng)絡(luò)服務(wù)名，需要在客戶端的tnsnames.ora文件里定義。control后的值是剛剛建的控制文件的路徑，日志將生成在log參數(shù)指定的位置上，如果有記錄沒有導(dǎo)入成功，這些記錄會放置在bad后指定的文件里。

怎么插入大量數(shù)據(jù)到Oracle數(shù)據(jù)庫中

可以使用oracle sql loader批量導(dǎo)入數(shù)據(jù)：

生成測試數(shù)據(jù)的EXCEL文件，把EXCEL文件另存為CSV（逗號分隔）（*.csv），控制文件設(shè)置為用逗號分隔。

示例：

LOAD DATA

INFILE ‘d：\car.csv’

APPEND INTO TABLE t_car_temp

FIELDS TERMINATED BY "，"

（phoneno，vip_car）

保存為input.ctl

最后在命令行下輸入：

C：\sqlldr userid=system/manager

control=input.ctl（在unix環(huán)境下亦同）

默認(rèn)日志文件名為：input.log

默認(rèn)壞記錄文件為：input.bad

Oracle數(shù)據(jù)的批量插入

前兩天接到一個需求——需要編程將SQL Server中的數(shù)據(jù)插入至Oracle 數(shù)據(jù)大約有 多萬條記錄 開始的時候我采取了直接構(gòu)建SQL插入的方式 結(jié)果耗時太長 為了提高性能我上網(wǎng)找了資料 最終采用DataAdapter批量插入至Oracle 提高了性能

代碼如下

一 直接構(gòu)建SQL語句插入

?

VB NET

???????? sw Start()

???????? Read Z J from SQL Server

???????? Dim sqlCmd As New SqlCommand()

???????? sqlCmd Connection = sqlConnection

???????? sqlCmd CommandText = SELECT * FROM? Z J

???????? Dim sqlDr As SqlDataReader

???????? sqlDr = sqlCmd ExecuteReader()

???????? Dim cmdInsertZ J As New OracleCommand()

???????? cmdInsertZ J Connection = oraConnection

???????? cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )

???????? Dim plantLever material oldMaterialNum materialDescription As Object

???????? While sqlDr Read()

???? ????????plantLever = ReadSqlDataReader(sqlDr )

???????????? material = ReadSqlDataReader(sqlDr )

???????????? oldMaterialNum = ReadSqlDataReader(sqlDr )

???????????? materialDescription = ReadSqlDataReader(sqlDr )

????????? ??? Insert to Oracle table Z J

???????????? cmdInsertZ J Parameters AddWithValue( :plantLever plantLever)

???????????? cmdInsertZ J Parameters AddWithValue( :material material)

???????????? cmdInsertZ J Parameters AddWithValue( :oldMaterialNum oldMaterialNum)

???????????? cmdInsertZ J Parameters AddWithValue( :materialDescription materialDescription)

???????????? cmdInsertZ J ExecuteNonQuery()

???????? End While

???????? sw Stop()

???????? Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())

?

二 采用DataAdapter實現(xiàn)批量插入

?

VB NET

???????? sw Start()

???????? Read Z J from SQL Server

???????? Dim sqlCmd As New SqlCommand()

???????? sqlCmd Connection = sqlConnection

???????? sqlCmd CommandText = SELECT * FROM? Z J

???????? Dim sqlDr As SqlDataReader

???????? sqlDr = sqlCmd ExecuteReader()

???????? Dim cmdInsertZ J As New OracleCommand()

???????? cmdInsertZ J Connection = oraConnection

??????? ?cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )

???????? Dim dtSqlZ J As New DataTable

???????? dtSqlZ J Columns Add( plantLever )

???????? dtSqlZ J Columns Add( material )

???????? dtSqlZ J Columns Add( oldMaterialNum )

???????? dtSqlZ J Columns Add( materialDescription )

???????? Dim plantLever material oldMaterialNum materialDescription As Object

???????? While sqlDr Read()

???????????? plantLever = ReadSqlDataReader(sqlDr )

???????????? material = ReadSqlDataReader(sqlDr )

???????????? oldMaterialNum = ReadSqlDataReader(sqlDr )

???????????? materialDescription = ReadSqlDataReader(sqlDr )

???????????? dtSqlZ J Rows Add(plantLever material oldMaterialNum materialDescription)

???????? End While

???????? sw Stop()

???????? Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())

???????? sw Start()

???????? Dim oraDa As New OracleDataAdapter()

???????? oraDa InsertCommand = cmdInsertZ J

???????? oraDa InsertCommand Parameters Add( :plantLever OracleType Char plantLever )

???????? oraDa InsertCommand Parameters Add( :material OracleType Char material )

???????? oraDa InsertCommand Parameters Add( :oldMaterialNum OracleType Char oldMaterialNum )

???????? oraDa InsertCommand Parameters Add( :materialDescription OracleType Char materialDescription )

???????? oraDa InsertCommand UpdatedRowSource = UpdateRowSource None

???????? oraDa UpdateBatchSize = ??? Adjust the batch size based on testing result

???????? oraDa Update(dtSqlZ J)

???????? sw Stop()

lishixinzhi/Article/program/Oracle/201311/18480