开发者社区> 嗯哼9925> 正文

SQL Server中bcp命令的用法以及数据批量导入导出

简介:
+关注继续查看

0.参考文献:

SQL Server BCP使用小结

bcp Utility

某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

1.bcp命令参数解析

bcp命令有许多参数,下面给出bcp命令参数的简要解析

复制代码
用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]         [-e 错误文件]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]             [-U 用户名]           [-P 密码]
  [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
  [-k 保留空值]               [-E 保留标识值]
  [-h"加载提示"]              [-x 生成xml 格式化文件]
复制代码

其中最常用的已经用粉红色字体标注。

2.bcp命令实例

这里我们以AdventureWorks样例数据库为例进行实验。

2.1.将表中数据导出到一个文件中(使用可信连接)

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c

上面的参数 out 表示输出文件,c:\Currency.dat是文件名和路径,-T表示可信连接,这个跟sqlcmd有点不同,在sqlcmd中使用-E表示可信连接。-c表示以字符形式输出,如果使用-w的话,输出内容相同,但是输出文件的大小将增加一倍。如果你要将导出的Currency.dat文件导入到非sql server数据库中,那么使用-w比较好。

2.2.将表中数据导出到一个文件中(使用混合模式身份验证)

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -c -Usa -Psa12345 -S.

这个数据导出语句与前面的不同之处是,前面使用可信连接,也就是windows验证,不需要输入用户名和密码。而此时使用的是sql server 验证方式,所以得输入sql server数据库用户名与密码。这里-S表示要连接的数据源,我这里-S.表示连接本地的默认实例,如果不加-S这个参数也会连接到默认实例中,如果要连接命名实例的话,可以使用参数:-S<server_name\instance_name>

2.3.将文件中的数据导入到表中

bcp Utility中提到:

如果使用 bcp 备份数据,请创建一个格式化文件来记录数据格式。 bcp 数据文件不包括任何架构或格式信息,因此如果已删除表或视图并且不具备格式化文件,则可能无法导入数据。

这句话的意思是,假如你要使用bcp来备份数据的话,那么最好也将数据的表结构也跟数据一起导出来。这样的话即使表被删除了,也可以通过先创建表,然后再使用bcp导入数据的方法进行还原。但是如果你只是用bcp备份数据,而没有备份表结构,那么当表被删除以后,你将无法使用bcp导入数据。

更深层的意思就是,如果你要使用bcp导入数据,那么必须有表结构。这个类似于insert into select的复制操作,因为它也需要先创建好表,然后再进行数据备份。具体可以参考:SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

所以假如我们要将前面导出的Currency.dat导入到数据库中,那么数据库中必须有对应的一张表,我们这里创建一张叫做Sales.Currency2的空表,sql语句如下: 

USE AdventureWorks;
GO
SELECT * INTO Sales.Currency2 
FROM AdventureWorks.Sales.Currency WHERE 1=2;--只创建表结构而不会插入数据

在创建好表结构以后,就可以将本地文件中的数据导入到数据库表中,导入的bcp命令如下:

bcp AdventureWorks.Sales.Currency2 in c:\Currency.dat -T -c

2.4.bcp中使用queryout关键词

如果要根据某种条件来导出数据的话,可以使用queryout关键字。

2.4.1.将特定的列复制到数据文件中

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout c:\Currency.Name.dat -T -c

2.4.2.将特定的行复制到数据文件中

bcp "select * from AdventureWorks.Sales.Currency where CurrencyCode='AED' and Name='Emirati Dirham'" queryout c:\Currency3.dat -T -c

3.大数据量的批量导入

bcp本身就可应用于大数据量的批量导入,不过他是命令行形式,如果要使用sql命令进行大数据量的批量导入,可以使用bulk insert,这个在之前的一篇博客中有提到,并进行了实验,可以参考:某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法。另外还找了一篇博客,里面列出了bulk insert和bcp的批量导入方式,详细请参考:SQL Server BCP使用小结

bcp并不只是只能在cmd命令行中执行,也可以在sql查询语句中执行,不过这需要调用一个存储过程。比如前面的将数据库表的数据导出到一个文件中,可以在SSMS中执行如下sql语句

exec master..xp_cmdshell 'bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c'

假如你在执行上述语句的时候报如下错误:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

你可以通过执行如下语句来解决问题:参考:xp_cmdshell Option

复制代码
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
复制代码

 

 本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2012/08/22/2651180.html,如需转载请自行联系原作者

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 批量完整备份
原文:SQL Server 批量完整备份 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 实现代码(SQL Codes) 实现方式一(One) 实现方式二(Two) 实现方式三(Three) 参考文献(References) 二.
1005 0
SQL Server 多表数据增量获取和发布 2.1
CDC介绍 cdc.png 为了满足数据迁移和数据抽取的业务需要,使得有机会在数据库层面上直接实现增量抽取功能,ORACLE综合性能和场景需要,在数据库引擎层面直接集成了CDC功能,由于提供了类似API的功能接口,变更数据捕获和更改跟踪均不要求在源中进行任何架构更改或使用触发器,所以比第三方工具具有一定的优势。
814 0
[重构 Swift 中单例的用法](Refactoring singleton usage in Swift)
本文讲的是[重构 Swift 中单例的用法](Refactoring singleton usage in Swift),在软件开发中,单例模式有足够的原因被广泛的不推荐和不赞成。它们难以测试或者说是不可能测试,当它们在其他类中隐式调用时会使你的代码库混乱,让代码难以复用。
1175 0
SQL Server修改表结构后批量更新所有视图
最近修改了数据库表结构,数据同步的时候出了问题,发现很多数据明明已经修改,但是通过视图筛选出来的还是原来的数据,所以怀疑应该是视图缓存了数据,在园子里找到下面的博文,在这里做个记录备忘。   原文链接:http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html   我们在使用SqlServer时经常遇到这种情况,当修改某个表的结构后,相关的视图就不对了而导致程序错误,因此就有个下面这个存储过程。
981 0
SQL2005/2008手工注入之批量爆数据for xml path
http://www.cqsec.com/read/SQL2005_2008_Injection_By_Hand_For_XML_Path ...
510 0
在SQL Server中将数据导出为XML和Json
原文:在SQL Server中将数据导出为XML和Json     有时候需要一次性将SQL Server中的数据导出给其他部门的也许进行关联或分析,这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP。
1050 0
SQL Server 多表数据增量获取和发布 4
核心代码分析 最关键的在于获取捕获表信息(系统表中间_CT结尾的数据)。 根据网上资料查取,找到了获取当前捕获表时间区间范围内数据的方式。 见[SQL Server 多表数据增量获取和发布 2.
805 0
C#使用linq查询大数据集的方法
这篇文章主要介绍了C#使用linq查询大数据集的方法,涉及C#调用linq进行数据查询的技巧,具有一定参考借鉴价值,需要的朋友可以参考下
44 0
+关注
4716
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载