155.bcp 实现二进制文件的导入导出

妖狐艹你老母 2024-02-19 19:15 53阅读 0赞

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_binaryIO]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_binaryIO]
    GO
    
    /*--bcp 实现二进制文件的导入导出
    
    	支持image,text,ntext字段的导入/导出
    	image适合于二进制文件,包括:Word文档,Excel文档,图片,音乐等
    	text,ntext适合于文本数据文件
    
    	注意:导入不会新增记录,所以导入前要对表进行初始化,即插入记录
    		导入时,将覆盖满足条件的所有行
    		导出时,将把所有满足条件的行导出到指定文件中
    		
    
    	此存储过程仅用bcp实现
    
    /*--调用示例
    --数据导出
    	exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat'
    
    --数据导入
    	exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat','',0
    --*/
    Create proc p_binaryIO
    @servename varchar (30),--服务器名称
    @username varchar (30), --用户名
    @password varchar (30),	--密码
    @tbname varchar (500), 	--数据库..表名
    @fdname varchar (30), 	--字段名
    @fname varchar (1000),	--目录+文件名,处理过程中要使用/覆盖:@filename+_temp
    @tj varchar (1000)='', 	--处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
    @isout bit=1			--1导出((默认),0导入
    AS 
    declare @fname_in varchar(1000)	--bcp处理应答文件名
    	,@fsize varchar(20)			--要处理的文件的大小
    	,@m_tbname varchar(50)		--临时表名
    	,@sql varchar(8000)
    
    --则取得导入文件的大小
    if @isout=1 
    	set @fsize='0'
    else
    begin
    	create table #tb(可选名 varchar(20),大小 int
    		,创建日期 varchar(10),创建时间 varchar(20)
    		,上次写操作日期 varchar(10),上次写操作时间 varchar(20)
    		,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int)
    	insert into #tb
    	exec master..xp_getfiledetails @fname
    	select @fsize=大小 from #tb
    	drop table #tb
    	if @fsize is null
    	begin
    		print '文件未找到'
    		return
    	end
    
    end
    
    --生成数据处理应答文件
    set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
    set @sql='select * into '+@m_tbname+' from(
    	select null as 类型
    	union all select 0 as 前缀
    	union all select '+@fsize+' as 长度
    	union all select null as 结束
    	union all select null as 格式
    	) a'
    exec(@sql)
    select @fname_in=@fname+'_temp'
    	,@sql='bcp "'+@m_tbname+'" out "'+@fname_in
    	+'" /S"'+@servename
    	+case when isnull(@username,'')='' then '' 
    		else '" /U"'+@username end
    	+'" /P"'+isnull(@password,'')+'" /c'
    exec master..xp_cmdshell @sql
    --删除临时表
    set @sql='drop table '+@m_tbname
    exec(@sql)
    
    if @isout=1
    begin
    	set @sql='bcp "select top 1 '+@fdname+' from ' 
    		+@tbname+case isnull(@tj,'') when '' then ''
    			else ' where '+@tj end
    		+'" queryout "'+@fname
    		+'" /S"'+@servename
    		+case when isnull(@username,'')='' then '' 
    			else '" /U"'+@username end
    		+'" /P"'+isnull(@password,'')
    		+'" /i"'+@fname_in+'"'
    	exec master..xp_cmdshell @sql
    end
    else
    begin
    	--为数据导入准备临时表
    	set @sql='select top 0 '+@fdname+' into '
    		+@m_tbname+' from ' +@tbname
    	exec(@sql)
    
    	--将数据导入到临时表
    	set @sql='bcp "'+@m_tbname+'" in "'+@fname
    		+'" /S"'+@servename
    		+case when isnull(@username,'')='' then '' 
    			else '" /U"'+@username end
    		+'" /P"'+isnull(@password,'')
    		+'" /i"'+@fname_in+'"'
    	exec master..xp_cmdshell @sql
    	
    	--将数据导入到正式表中
    	set @sql='update '+@tbname
    		+' set '+@fdname+'=b.'+@fdname
    		+' from '+@tbname+' a,'
    		+@m_tbname+' b'
    		+case isnull(@tj,'') when '' then ''
    			else ' where '+@tj end
    	exec(@sql)
    
    	--删除数据处理临时表
    	set @sql='drop table '+@m_tbname
    end
    
    --删除数据处理应答文件
    set @sql='del '+@fname_in
    exec master..xp_cmdshell @sql
    go