oracle批量导入

 

在实际的操作中，我们可能会遇到将相同或不同目录下文件中的大批量数据同时导入到数据库中的操作，一般情况下，你可能会通过insert的方式来解决批量数据的导入问题，但是在导入的速度和对数据库性能方面的影响却是不容乐观的，那有没有一种导入性能相对较好的方法呢？答案是肯定的，建议不要用insert的方法导入大批量的数据操作，除非必要情况下才使用。而UTS和BCP的方法在这种情况下将满足你需求的灵活性和速度。下面我们就来看看其实现的过程吧。
    本示例的测试数据库为SQL Server2005。
 
一、测试准备
1、启用bcp导入导出功能
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'xp_cmdshell',1
reconfigure
2、测试文件数据准备
   首先向准备测试的环境，本例中创建文件目录“c:\test”，test文件夹中放入三个文件a.csv、b.csv和c.csv，其文件的内容如下：
a.csv内容：
1000 0001,取出,600.00,2012-05-09
1000 0002,存入,2000.00,2012-11-23
1000 0003,存入,3000.00,2012-12-11
1000 0004,取出,1000.00,2012-12-12
b.csv内容：
1000 0005,取出,500.00,2012-12-16
1000 0006,存入,2500.00,2012-12-11
1000 0007,存入,6000.00,2012-12-13
1000 0008,取出,2000.00,2012-12-06
c.csv内容：
1000 0009,取出,1000.00,2012-12-06
1000 0010,存入,900.00,2012-12-01
1000 0011,存入,800.00,2012-12-13
1000 0012,取出,500.00,2012-12-11
注：文件中日期为不完整时（如：2012-12-6），bcp方式导入时，由于表中日期数据格式的控制，该条数据无法正常导入，需改为（如：2012-12-06）即可。
二、创建测试表
1、创建需要导入数据的表
use pTest
go
create table transinfo
(
cardID varchar(10),
transtype varchar(6),
transMoey money,
transDate datetime default getdate()
)
2、创建导入数据记录日志表
use pTest
go
create table logtable
(id int identity(1,1),
content varchar(200),
impdate datetime default getdate()
)
三、测试批量数据导入存储过程
1、bulk(UTS)批量导入数据存储过程
if object_id('p_bulk')is not null
drop proc p_bulk
go
create proc p_bulk
@filepath varchar(300),
@pattern varchar(20),
@tablename varchar(60)
as
set quoted_identifier off
declare @bulk_cmd varchar(600)
declare @max1 int
declare @count1 int
declare @filename varchar(80)
set @count1=0
create table #t(name1 varchar(200))
set @bulk_cmd='master.dbo.xp_cmdshell "dir '+@filepath+@pattern+' /b"'
print @bulk_cmd
insert  #t exec (@bulk_cmd)
delete from #t where name1 is null
select identity(int,1,1) id,name1 into #y from #t
drop table #t
set @max1=(select max(id) from #y)
while @count1<=@max1
begin
set @count1=@count1+1
set @filename=(select name1 from #y where id=@count1)
set @bulk_cmd='bulk insert '+@tablename+' from "'+@filepath+@filename+'" with (fieldterminator='','',rowterminator= ''\n'')'   --fieldterminator表示列分隔符，rowterminator表示行分隔符
print @bulk_cmd
exec (@bulk_cmd)
insert into logtable(content) select @bulk_cmd
delete from logtable where content is null
end
drop table #y
2、bcp批量导入数据存储过程
if object_id('p_bcp')is not null
drop proc p_bcp
go
create proc p_bcp
@servername varchar(10),
@dbname varchar(10),
@filepath varchar(300),
@pattern varchar(20),
@tablename varchar(60)
as
set quoted_identifier off
declare @bcp_cmd varchar(600)
declare @max1 int
declare @count1 int
declare @filename varchar(80)
set @count1=0
create table #t(name1 varchar(200))
set @bcp_cmd='master.dbo.xp_cmdshell "dir '+@filepath+@pattern+' /b"'
print @bcp_cmd
insert  #t exec (@bcp_cmd)
delete from #t where name1 is null
select identity(int,1,1) id,name1 into #y from #t
drop table #t
set @max1=(select max(id) from #y)
while @count1<=@max1
begin
set @count1=@count1+1
set @filename=(select name1 from #y where id=@count1)
set @bcp_cmd='master.dbo.xp_cmdshell ''bcp "'+@dbname+'.dbo.'+@tablename+'" in "'+@filepath+@filename+'" -
S'+@servername+' -T -c -r\n -t,'''    --  -r\n表示行分隔符，-t,表示行分隔符
print @bcp_cmd
exec (@bcp_cmd)
insert into logtable(content) select @bcp_cmd
delete from logtable where content is null
end
drop table #y
四、测试结果
1、p_bulk存储过程测试
通过执行语句
exec p_bulk 'c:\test\','*.csv','transinfo'
说明：该语句中 '*.csv'表示该文件夹下后缀为csv的所有文件，你可以改为指定文件（如：'a.csv'）；也可以改为所有文件（如：'*.*'）。
得到如下结果：
select * from transinfo
cardID             transType transMoney            transDate
------------------ --------- --------------------- -----------------------
10000001           取出        600.00                2012-05-09 00:00:00.000
10000002           存入        2000.00               2012-11-23 00:00:00.000
10000003           存入        3000.00               2012-12-11 00:00:00.000
10000004           取出        1000.00               2012-12-12 00:00:00.000
1000 0005          取出        500.00                2012-12-16 00:00:00.000
1000 0006          存入        2500.00               2012-12-11 00:00:00.000
1000 0007          存入        6000.00               2012-12-13 00:00:00.000
1000 0008          取出        2000.00               2012-12-06 00:00:00.000
1000 0009          取出        1000.00               2012-12-06 00:00:00.000
1000 0010          存入        900.00                2012-12-01 00:00:00.000
1000 0011          存入        800.00                2012-12-13 00:00:00.000
1000 0012          取出        500.00                2012-12-11 00:00:00.000
select * from logtable
id                  content