bcp命令是SQL Server提供的一个快捷的数据导入导出工具使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据bcp是SQL Server中负责导入导出数据的一个命令行工具它是基于DBLibrary的并且能以并行的方式高效地导入导出大批量的数据bcp可以将数据库的表或视图直接导出也能通过SELECT FROM语句对表或视图进行过滤后导出在导入导出数据时可以使用默认值或是使用一个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中
bcp的主要参数介绍
bcp共有四个动作可以选择
() 导入
这个动作使用in命令完成后面跟需要导入的文件名
() 导出
这个动作使用out命令完成后面跟需要导出的文件名
() 使用SQL语句导出
这个动作使用queryout命令完成它跟out类似只是数据源不是表或视图名而是SQL语句
() 导出格式文件
这个动作使用format命令完成后而跟格式文件名
下面介绍一些常用的选项
f format_file
format_file表示格式文件名这个选项依赖于上述的动作如果使用的是in或outformat_file表示已经存在的格式文件如果使用的是format则表示是要生成的格式文件
x
这个选项要和f format_file配合使用以便生成xml格式的格式文件
F first_row
指定从被导出表的哪一行导出或从被导入文件的哪一行导入
L last_row
指定被导出表要导到哪一行结束或从被导入文件导数据时导到哪一行结束
c
使用char类型做为存储类型没有前缀且以\t做为字段分割符以\n做为行分割符
w
和c类似只是当使用Unicode字符集拷贝数据时使用且以nchar做为存储类型
t field_term
指定字符分割符默认是\t
r row_term
指定行分割符默认是\n
S server_name[ \instance_name]
指定要连接的SQL Server服务器的实例如果未指定此选项bcp连接本机的SQL Server默认实例如果要连接某台机器上的默认实例只需要指定机器名即可
U login_id
指定连接SQL Sever的用户名
P password
指定连接SQL Server的用户名密码
T
指定bcp使用信任连接登录SQL Server如果未指定T必须指定U和P
k
指定空列使用null值插入而不是这列的默认值
如何使用bcp导出数据
() 使用bcp导出整个表或视图
bcp AdventureWorkssalescurrency out c:\currencytxt c Usa Ppassword 使用密码连接或bcp AdventureWorkssalescurrency out c:\currencytxt c T 使用信任连接
下面是上述命令执行后的输出结果
Starting copy
rows copied
Network packet size (bytes):
Clock Time (ms) Total : Average : ( rows per sec)
下面是currencytxt的部分内容
AED Emirati Dirham ::
AFA Afghani ::
ZWD Zimbabwe Dollar ::
在使用密码登录时需要将U后的用户名和P后的密码加上双引号
注bcp除了可以在控制台执行外还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行bcp如上述第一条命令可改写为
EXEC masterxp_cmdshell bcp AdventureWorkssalescurrency out c:\currencytxt c Usa Ppassword
执行xp_cmdshell后返回信息以表的形式输出为了可以方便地在SQL中执行bcp下面的命令都使用xp_cmdshell执行bcp命令
() 对要导出的表进行过滤
bcp不仅可以接受表名或视图名做为参数也可以接受SQL做为参数通过SQL语句可以对要导出的表进行过滤然后导出过滤后的记录
EXEC masterxp_cmdshell bcp SELECT TOP * FROM AdventureWorkssalescurrency queryout c:\currencytxt c Usa Ppassword
bcp还可以通过简单地设置选项对导出的行进行限制
这条命令使用了两个参数F 和L 表示从SE
EXEC masterxp_cmdshell bcp SELECT TOP * FROM AdventureWorkssalescurrency queryout c:\currencytxt F L c Usa Ppassword
LECT TOP * FROM AdventureWorkssalescurrency所查出来的结果中取第条到条记录进行导出
如何使用bcp导出格式文件
bcp不仅可以根据表视图导入导出数据还可以配合格式文件对导入导出数据进行限制格式文件以纯文本文件形式存在分为一般格式和xml格式用户可以手工编写格式文件也可以通过bcp命令根据表视图自动生成格式文件
EXEC masterxp_cmdshell bcp AdventureWorkssalescurrency format nul f c:\currency_formatfmt c T
上述命令将currency表的结构生成了一个格式文件currency_formatfmt下面是这个格式文件的内容
SQLCHAR \t CurrencyCode SQL_Latin_General_CP_CI_AS
SQLCHAR \t Name SQL_Latin_General_CP_CI_AS
SQLCHAR \r\n ModifiedDate
这个格式文件记录了这个表的字段（共个字段）类型长度字符和行分割符和字段名等信息
bcp还可以通过x选项生成xml格式的格式文件
EXEC masterxp_cmdshell bcp AdventureWorkssalescurrency format nul f c:\currency_formatfmt x c T
xml格式文件所描述的内容和普通格式文件所描述的内容完全一样只是格式不同
如何使用bcp导入数据
bcp可以通过in命令将上面所导出的currencytxt和currencytxt再重新导入到数据库中由于currency有主键因此我们将复制一个和currency的结构完全一样的表
SELECT TOP * INTO AdventureWorkssalescurrency FROM AdventureWorkssalescurrency
将数据导入到currency表中
EXEC masterxp_cmdshell bcp AdventureWorkssalescurrency in c:\currencytxt c T
导入数据也同样可以使用F和L选项来选择导入数据的记录行
EXEC masterxp_cmdshell bcp AdventureWorkssalescurrency in c:\currencytxt c F L T
在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中不满足则不导入如上述的格式文件中的第三个字段的字符长度是如果某个文本文件中的相应字段的长度超过则这条记录将不被导入到数据库中其它满足条件的记录正常导入
