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
复制点帮助给你看 将数据从数据文件复制到 SQL Server 若要将数据文件大容量复制到 Microsoft® SQL Server™ 实例，请遵循以下准则： 将数据大容量复制到没有索引的表中时，如果在通常情况下使用完整恢复模式，则将恢复模式设置为大容量日志记录模式。 这是推荐的方法，因为日志不记录行插入操作，所以有助于防止事务日志耗尽空间。系统管理员或数据库所有者可以设置此选项。有关更多信息，请参见有日志记录和最小日志记录的大容量复制操作。如果要装载的数据与表中已存在的数据相比数量较大，在执行大容量复制操作之前删除表的索引会提高大容量复制速度。 反之，如果要装载的数据与表中已存在的数据相比数量较小，则不必删除索引，因为重建索引的时间可能比执行大容量复制操作的时间要长。有关更多信息，请参见优化大容量复制性能。 确保用 bcp（或使用 BULK INSERT 语句时的查询工具）登录到 SQL Server 时所用的用户帐户对表具有 SELECT 和 INSERT 权限（由表的所有者指派）。 说明 只有 sysadmin 固定服务器角色的成员可以执行 BULK INSERT 语句。如果恢复模式是简单模式，则建议使用完整备份或差异备份；如果恢复模式是大容量日志记录模式或完整恢复模式，使用日志备份就足够了。有关更多信息，请参见备份和还原操作。 为了使用 bcp 实用工具或 BULK INSERT 语句将数据从数据文件成功地大容量复制到表中，必须了解并指定数据文件中的终止符。 说明 当试图将数据大容量复制到 SQL Server 实例中时，ASCII 数据文件中的隐藏字符可能导致问题，出现"发现意外的空值"错误信息。许多实用工具和文本编辑器可显示隐藏字符，通常可以在数据文件的底部找到这些字符。找到并删除这些字符可以解决此问题。 Newpubs.dat 文件为：1111,Stone Age Books,Boston,MA,USA 2222 ,Harley & Davidson,Washington,DC,USA 3333 ,Infodata Algosystems,Berkeley,CA,USA因为该数据文件均为字符数据，所以需要指定以下选项和开关。大容量复制选项 bcp 实用工具开关 BULK INSERT 子句 字符模式格式 -c DATAFILETYPE = 'char' 字段终止符 -t FIELDTERMINATOR 行终止符 -r ROWTERMINATOR 在 Newpubs.dat 文件中，行中每个字段均以逗号 (,) 结束，而每行均以换行符 (\n) 结束。下例中的 publishers2 表可以通过执行以下命令创建：USE pubs GO SELECT * INTO publishers2 FROM publishers GO若要将数据从 Newpubs.dat 大容量复制到 publishers2，可以使用以下命令：bcp pubs..publishers2 in newpubs.dat -c -t , -r \n -Sservername -Usa -Ppassword另外，也可以在 SQL 查询分析器这样的查询工具中使用 BULK INSERT 语句来大容量复制数据：BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )Newpubs.dat 文件中的数据现在已追加到 publishers2 中：Pub_id pub_name city state Country ------ ---------------- ---------- ----- ----- 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA 1111 Stone Age Books Boston MA USA 1389 Algodata Infosystems Berkeley CA USA 1622 Five Lakes Publishing Chicago IL USA 1756 Ramona Publishers Dallas TX USA 2222 Harley & Davidson Washington DC USA 3333 Infodata Algosystems Berkeley CA USA 9901 GGG&G München Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris France 复制包含标识值的数据 bcp 实用工具和 BULK INSERT 语句允许将包含标识值的数据文件大容量复制到 SQL Server 实例中。为防止 SQL Server 提供标识值，bcp 实用工具接受 -E 开关，并且 BULK INSERT 语句接受 KEEPIDENTITY 子句。当数据文件中的行大容量复制到表中时，SQL Server 不会自动地指派唯一标识值；标识值将从数据文件中获得。如果未提供这些选项，则将忽略被导入数据文件中标识符列的值，而是由 SQL Server 根据表创建期间指定的种子值和增量值自动指派唯一值。如果数据文件不包含表中标识符列的值，则使用格式文件指定导入数据时应跳过的标识符列。SQL Server 自动赋给该列唯一值。导入 image 数据 可以将数据文件作为 image 数据大容量复制到 SQL Server 实例中。使用 bcp 实用工具将数据文件 Test.doc 装载到 pubs 数据库的 bitmap 表中的命令是：bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservernamebcp 将提示：Enter the file storage type of field c1 [image]: Enter the prefix length of field c1 [4]: 0 Enter length of field c1 [4096]: 5578 Enter the field terminator [none]:在该示例中，数据文件将装载到列 c1，而 5578 是数据文件的长度。使用 BULK INSERT 语句时，需要首先创建格式文件，然后用该文件提供格式信息。若要创建格式文件，请使用 bcp 实用工具：bcp pubs..bitmap out c:\bitmap.txt -Sservername -Usa -Ppasswordbcp 实用工具提示输入 bitmap 中每一列的文件存储类型、前缀长度、字段长度和字段终止符。下表列出了 c1 列的值。提示 值 文件存储类型 Image 前缀长度 0 字段长度 5578 字段终止符 无 Bcp.fmt 文件的内容如下：8.0 1 1 SQLIMAGE 0 5578 "" 1 c1使用 BULK INSERT 语句将 Test.doc 数据文件大容量复制到 pubs 数据库的 bitmap 表中时，请从 SQL 查询分析器这样的查询工具中执行：BULK INSERT pubs..bitmap FROM 'c:\test.doc' WITH ( FORMATFILE = 'c:\Bcp.fmt' )
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
将数据从数据文件复制到 SQL Server
若要将数据文件大容量复制到 Microsoft® SQL Server™ 实例，请遵循以下准则： 将数据大容量复制到没有索引的表中时，如果在通常情况下使用完整恢复模式，则将恢复模式设置为大容量日志记录模式。
这是推荐的方法，因为日志不记录行插入操作，所以有助于防止事务日志耗尽空间。系统管理员或数据库所有者可以设置此选项。有关更多信息，请参见有日志记录和最小日志记录的大容量复制操作。如果要装载的数据与表中已存在的数据相比数量较大，在执行大容量复制操作之前删除表的索引会提高大容量复制速度。
反之，如果要装载的数据与表中已存在的数据相比数量较小，则不必删除索引，因为重建索引的时间可能比执行大容量复制操作的时间要长。有关更多信息，请参见优化大容量复制性能。 确保用 bcp（或使用 BULK INSERT 语句时的查询工具）登录到 SQL Server 时所用的用户帐户对表具有 SELECT 和 INSERT 权限（由表的所有者指派）。
说明 只有 sysadmin 固定服务器角色的成员可以执行 BULK INSERT 语句。如果恢复模式是简单模式，则建议使用完整备份或差异备份；如果恢复模式是大容量日志记录模式或完整恢复模式，使用日志备份就足够了。有关更多信息，请参见备份和还原操作。
为了使用 bcp 实用工具或 BULK INSERT 语句将数据从数据文件成功地大容量复制到表中，必须了解并指定数据文件中的终止符。
说明 当试图将数据大容量复制到 SQL Server 实例中时，ASCII 数据文件中的隐藏字符可能导致问题，出现"发现意外的空值"错误信息。许多实用工具和文本编辑器可显示隐藏字符，通常可以在数据文件的底部找到这些字符。找到并删除这些字符可以解决此问题。
Newpubs.dat 文件为：1111,Stone Age Books,Boston,MA,USA
2222 ,Harley & Davidson,Washington,DC,USA
3333 ,Infodata Algosystems,Berkeley,CA,USA因为该数据文件均为字符数据，所以需要指定以下选项和开关。大容量复制选项 bcp 实用工具开关 BULK INSERT 子句
字符模式格式 -c DATAFILETYPE = 'char'
字段终止符 -t FIELDTERMINATOR
行终止符 -r ROWTERMINATOR
在 Newpubs.dat 文件中，行中每个字段均以逗号 (,) 结束，而每行均以换行符 (\n) 结束。下例中的 publishers2 表可以通过执行以下命令创建：USE pubs
GO
SELECT * INTO publishers2 FROM publishers
GO若要将数据从 Newpubs.dat 大容量复制到 publishers2，可以使用以下命令：bcp pubs..publishers2 in newpubs.dat -c -t , -r \n -Sservername -Usa -Ppassword另外，也可以在 SQL 查询分析器这样的查询工具中使用 BULK INSERT 语句来大容量复制数据：BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)Newpubs.dat 文件中的数据现在已追加到 publishers2 中：Pub_id pub_name city state Country
------ ---------------- ---------- ----- -----
0736 New Moon Books Boston MA USA
0877 Binnet & Hardley Washington DC USA
1111 Stone Age Books Boston MA USA
1389 Algodata Infosystems Berkeley CA USA
1622 Five Lakes Publishing Chicago IL USA
1756 Ramona Publishers Dallas TX USA
2222 Harley & Davidson Washington DC USA
3333 Infodata Algosystems Berkeley CA USA
9901 GGG&G München Germany
9952 Scootney Books New York NY USA
9999 Lucerne Publishing Paris France
复制包含标识值的数据
bcp 实用工具和 BULK INSERT 语句允许将包含标识值的数据文件大容量复制到 SQL Server 实例中。为防止 SQL Server 提供标识值，bcp 实用工具接受 -E 开关，并且 BULK INSERT 语句接受 KEEPIDENTITY 子句。当数据文件中的行大容量复制到表中时，SQL Server 不会自动地指派唯一标识值；标识值将从数据文件中获得。如果未提供这些选项，则将忽略被导入数据文件中标识符列的值，而是由 SQL Server 根据表创建期间指定的种子值和增量值自动指派唯一值。如果数据文件不包含表中标识符列的值，则使用格式文件指定导入数据时应跳过的标识符列。SQL Server 自动赋给该列唯一值。导入 image 数据
可以将数据文件作为 image 数据大容量复制到 SQL Server 实例中。使用 bcp 实用工具将数据文件 Test.doc 装载到 pubs 数据库的 bitmap 表中的命令是：bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservernamebcp 将提示：Enter the file storage type of field c1 [image]:
Enter the prefix length of field c1 [4]: 0
Enter length of field c1 [4096]: 5578
Enter the field terminator [none]:在该示例中，数据文件将装载到列 c1，而 5578 是数据文件的长度。使用 BULK INSERT 语句时，需要首先创建格式文件，然后用该文件提供格式信息。若要创建格式文件，请使用 bcp 实用工具：bcp pubs..bitmap out c:\bitmap.txt -Sservername -Usa -Ppasswordbcp 实用工具提示输入 bitmap 中每一列的文件存储类型、前缀长度、字段长度和字段终止符。下表列出了 c1 列的值。提示 值
文件存储类型 Image
前缀长度 0
字段长度 5578
字段终止符 无
Bcp.fmt 文件的内容如下：8.0
1
1 SQLIMAGE 0 5578 "" 1 c1使用 BULK INSERT 语句将 Test.doc 数据文件大容量复制到 pubs 数据库的 bitmap 表中时，请从 SQL 查询分析器这样的查询工具中执行：BULK INSERT pubs..bitmap FROM 'c:\test.doc'
WITH (
FORMATFILE = 'c:\Bcp.fmt'
)