bcp处理文件到数据库

因为业务的实时性，需要处理另一方业务产生的日志数据，用来查错、核对数据。思路较简单,因为日志内容生成还较规则，所有用下面的方法处理。

 

--启用xp_cmdshell

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

--EXEC sp_configure;

EXEC sp_configure 'xp_cmdshell', '1';

RECONFIGURE WITH OVERRIDE;

 

--创建原始日志表

create table Log_temp(id int identity(1,1),log varchar(4000),filename varchar(20))

 

--创建文件名表

create table Log_filename(id int identity(1,1),filename varchar(20))

 

--取所有文件名存入临时表

declare @log_path varchar(50),@filetype varchar(20)

set @log_path='D:/log/'  --日志所在路径

set @filetype='log'  --日志文件类型

create table #log_filename(id int identity(1,1),filename varchar(200))

insert into #log_filename

exec ('exec  master..xp_cmdshell ' + '''dir  '+@log_path + '*.' + @filetype + '''')

 

--文件名存入正式表

insert into Log_filename

select ltrim(right(filename,16)) from #log_file where right(filename,4)='.log'  and ltrim(right(filename,16)) like'LCS%' --有效文件才导入

order by ltrim(right(filename,16))

 

declare @filename varchar(30),--导入的文件名

    @path varchar(50),--当前日志所在路径

    @servername varchar(30),--服务器名

    @dbname varchar(30),--数据名

    @uid varchar(20),--数据库登陆用户

    @pwd varchar(30), --登陆密码

    @tbname varchar(30),--表名

    @sql varchar(2000)

 

set @filename=''

set @path='D:/log/'

set @servername='cxj'

set @dbname='hislbs'

set @tbname='Log_temp'

set @uid='sa'

set @pwd=''

set @sql=''

 

--循环取出文件名,导入数据

declare cur_1 cursor for select filename  from Log_filename

open cur_1

fetch next from cur_1 into @filename

while @@fetch_status=0

begin

    set @sql='bcp '+@dbname+'..'+@tbname+' in '+@path+@filename+' -c -S'+@servername+' -U'+@uid+' -P'+@pwd

    exec master..xp_cmdshell @sql

    fetch next from cur_1 into @filename

end

close cur_1

deallocate cur_1

 

--分别按业务类型代码，取所需要数据

create table #log_101_out(id int identity(1,1),log nvarchar(2000))

insert into #log_101_out 

select substring(log,36,len(log)-36) from Log_temp where log like'%OU_01%' and left(substring(log,34,len(log)-34),1)=1

 

--处理格式,||间没有数值,组成"insert into(a) values("a")格式"

update #log_101_out set log='''' + substring(replace(replace(log,'||','|0|'),'|',''','''),1,len(replace(replace(log,'||','|0|'),'|',''','''))-2)

 

--存储数据

declare @sql varchar(2000),@id int

set @sql=''

declare cur_1 cursor for select id from #log_01 

open cur_1

fetch next from cur_1 into @id

while @@fetch_status=0

begin

    select @sql=log from #log_101_out where id=@id

    exec('insert into log_101_out values( '+@sql + ')')

    fetch next from cur_1 into @id

end

close cur_1

deallocate cur_1

 

--关闭xp_cmdshell

USE master;

GO

EXEC sp_configure 'show advanced option', '0';

RECONFIGURE;

--EXEC sp_configure;

EXEC sp_configure 'xp_cmdshell', '0';

RECONFIGURE WITH OVERRIDE;