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实现--邹建 2003.08(引用请保留此信息)---*//*--调用示例 --数据导出 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 endend--生成数据处理应答文件 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 @sqlgo
Yes, it is support as XML Data Type in Yukon-------------- SQL Server 2005 introduces a native data type called XML. A user can create a table with one or more columns of type XML besides relational columns; XML variables and parameters are also allowed. XML values are stored in an internal format as large binary objects (BLOB) in order to support the XML model characteristics more faithfully such as document order and recursive structures.-----------------see the doc below, XML Support in Microsoft SQL Server 2005 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp
create table #tb (doc varchar(8000))
bulk insert #tb from 'c:\a.xml'--将读取到的数据保存到变量中
set @doc=''
select @doc=@doc+doc from #tb--删除临时表
drop table #tb/*--xml文档内容
SET @doc ='
<?xml version=''1.0'' encoding="gb2312"?>
<LanguageSource>
<Book>
<type>报告文学</type>
<author>不详</author>
<title>不详</title>
<context>
<chapter>
<title>不详1</title>
<doc>不详1</doc>
</chapter>
<chapter>
<title>不详2</title>
<doc>不详2</doc>
</chapter>
</context>
</Book>
</LanguageSource>'
--*/
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc--创建测试的表
create table 作者表(作者ID int identity(1,1),作者名字 varchar(50),作者介绍 varchar(50))
create table 类型表(类型ID int identity(1,1),类型名 varchar(50))
create table 书名列表(书名ID int identity(1,1),作者ID int,类型ID int,书名 varchar(50))--下面是数据处理
declare @作者ID int,@类型ID int--作者表:作者ID、作者名字、作者介绍
insert 作者表(作者名字)
SELECT * FROM OPENXML (@idoc, '/LanguageSource/Book',2)
WITH (
作者名字 varchar(50) 'author'
)
set @作者id=@@identity--类型表:类型ID、类型名
insert into 类型表(类型名)
SELECT * FROM OPENXML (@idoc, '/LanguageSource/Book',2)
WITH (
类型名 varchar(50) 'type'
)
set @类型ID=@@identity--书名列表:书名ID、作者ID、类型ID、书名(也就是XML中的第一个TITLE)
insert into 书名列表(作者ID,类型ID,书名)
SELECT @作者ID,@类型ID,* FROM OPENXML (@idoc, '/LanguageSource/Book',2)
WITH (
书名 varchar(50) 'title'
)EXEC sp_xml_removedocument @idocgo--显示处理结果
select * from 作者表
select * from 类型表
select * from 书名列表--删除测试表
drop table 作者表,类型表,书名列表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_textcopy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_textcopy]
GOCREATE PROCEDURE sp_textcopy
@srvname varchar (30), --服务器名
@login varchar (30), --用户名
@password varchar (30), --密码
@dbname varchar (30), --数据库名
@tbname varchar (30), --表名
@colname varchar (30), --存储文件的字段名
@filename varchar (30), --要存取的文件名
@whereclause varchar (40), --条件
@direction char(1) --I 为存储到数据库,O 从数据库中读出成文件
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S"' + @srvname +
'" /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F "' + @filename +
'" /' + @direction
EXEC master..xp_cmdshell @exec_str
go--调用示例:
--1.创建示例表和初始化数据
create table tb(id int,img image)
insert tb
select 1,0x
union all select 2,0x
go--2.将文件保存到数据库中
declare @srv varchar(255),@db sysname,@tb sysname,@col sysname
select @srv=@@servername --服务器名
,@db=db_name() --数据库名
,@tb='tb' --表名
,@col='img' --列名
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.jpg','where id=1','I' --保存到id=1的记录中,注意条件是:id=1
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.doc','where id=2','I' --保存到id=2的记录中,注意条件是:id=2--3.从数据库中读出保存为文件
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.jpg','where id=1','O' --读出id=1的记录,注意条件是:id=1
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.doc','where id=2','O' --读出id=2的记录,注意条件是:id=2--删除测试环境
drop table tb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_textcopy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_textcopy]
drop procedure [dbo].[p_binaryIO]
GO/*--bcp 实现二进制文件的导入导出 支持image,text,ntext字段的导入/导出
image适合于二进制文件,包括:Word文档,Excel文档,图片,音乐等
text,ntext适合于文本数据文件 注意:导入不会新增记录,所以导入前要对表进行初始化,即插入记录
导入时,将覆盖满足条件的所有行
导出时,将把所有满足条件的行导出到指定文件中
此存储过程仅用bcp实现--邹建 2003.08(引用请保留此信息)---*//*--调用示例
--数据导出
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
endend--生成数据处理应答文件
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 @sqlgo
参考:
利用存储过程存取图片等二进制文件
原理:
在存储过程中调用使用扩展存储过程xp_cmdshell调用外部命令行工具textcopy.exe,实现图片等二进制文件的存取。方法:
1、建立过程
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str 调用示例建表和初始化数据create table 表名 (编号 int,image列名 image)
go
insert 表名 values(1,0x)
insert 表名 values(2,0x)
go读入文件sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','I' --注意条件是 编号=1sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','I' --注意条件是 编号=2go读出成文件sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','O' --注意条件是 编号=1sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','O' --注意条件是 编号=2go
把image列清空:update 表 set 列=null
SQL Server 2005 introduces a native data type called XML. A user can create a table with one or more columns of type XML besides relational columns; XML variables and parameters are also allowed. XML values are stored in an internal format as large binary objects (BLOB) in order to support the XML model characteristics more faithfully such as document order and recursive structures.-----------------see the doc below, XML Support in Microsoft SQL Server 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xml.asp