笔记281 BCP命令
1 BCP命令(BULK COPY PROGRAM)：
2
3 BCP的主要参数介绍
4
5 BCP共有四个动作可以选择。
6 (1) 导入。
7 这个动作使用in命令完成，后面跟需要导入的文件名。
8 (2) 导出。
9 这个动作使用out命令完成，后面跟需要导出的文件名。
10 (3) 使用SQL语句导出。
11 这个动作使用queryout命令完成，它跟out类似，只是数据源不是表或视图名，而是SQL语句。
12 (4) 导出格式文件。
13 这个动作使用format命令完成，后而跟格式文件名。
14
15 下面介绍一些常用的选项：
16
17 -f format_file
18 format_file表示格式文件名。这个选项依赖于上述的动作，如果使用的是in或out，format_file表示已经存在的格式文件，如果使用的是format则表示是要生成的格式文件。
19
20 -x
21 这个选项要和-f format_file配合使用，以便生成xml格式的格式文件。
22
23 -F first_row
24 指定从被导出表的哪一行导出，或从被导入文件的哪一行导入。
25
26 -L last_row
27 指定被导出表要导到哪一行结束，或从被导入文件导数据时，导到哪一行结束。
28
29 -c
30 使用char类型做为存储类型，没有前缀且以"\t"做为字段分割符，以"\n"做为行分割符。
31
32 -w
33 和-c类似，只是当使用Unicode字符集拷贝数据时使用，且以nchar做为存储类型。
34
35 -t field_term
36 指定字符分割符，默认是"\t"。
37
38 -r row_term
39 指定行分割符，默认是"\n"。
40
41 -S server_name[ \instance_name]
42 指定要连接的SQL Server服务器的实例，如果未指定此选项，BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例，只需要指定机器名即可。
43
44 -U login_id
45 指定连接SQL Sever的用户名。
46
47 -P password
48 指定连接SQL Server的用户名密码。
49
50
51
52 --设置字段分隔符和行分隔符(-c -t"," -r"\n"),不想输入字段类型等请配合-c一起使用
53 -r row_term 行分隔符
54 指定行分割符，默认是"\n"。
55
56 -t field_term
57 指定字符分割符，默认是"\t"。
58
59 bcp "select * from 数据库名.dbo.表名" queryout c:\currency.txt -S 数据库实例 -U"用户" -P"密码" -c -t"," -r"\n"
60 EXEC master..xp_cmdshell 'bcp "select * from GPOSDB.dbo.ST_OperationLOG" queryout c:\currency.txt -S joe -U sa -P test -c -t"," -r"\n"'
61
62
63 --在查询分析器上执行(EXEC master..xp_cmdshell)
64 EXEC master..xp_cmdshell 'bcp "select * from 数据库名.dbo.表名" queryout c:\currency.txt -S 数据库实例 -U"用户" -P"密码" -c'
65
66 EXEC master..xp_cmdshell 'bcp "select * from GPOSDB.dbo.ST_OperationLOG" queryout c:\currency.txt -S joe -U sa -P test -c'
67
68 --员工加油记录 表链接
69 exec master.. xp_cmdshell 'bcp " select * from gposdb.dbo.CT_FuelingData a inner join gposdb.dbo.CT_InhouseCard b on a.VC_FD_Cardno = b.VC_IC_CardNO " queryout d:\empfueldata.xls -c -U "sa" -P "test"'
70
71 --将数据导入到currency表中
72 T
73 指定BCP使用信任连接登录SQL Server。如果未指定-T，必须指定-U和-P
74 EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:\currency.txt -c -T'
75
76
77 --导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
78
79 -F first_row
80 -L last_row
81 所查出来的结果中取第10条到13条记录进行导出。
82
83 EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:\currency.txt -c -F 10 -L 13 -T'
84
85 BCP还可以通过-x选项生成xml格式的格式文件。
86
87 EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:\currency_format2.fmt -x -c -T'
88
89
90 --将数据导出为Excel
91 EXEC master..xp_cmdshell 'bcp "select * from GPOSDB.dbo.ST_OperationLOG" queryout c:\currency.xls -T -c '
92
93
94 --指定每批导入数据的行数、指定服务器发出或接收的每个网络数据包的字节数(-k -b5000 -a65535)
95 bcp "select * from 数据库名.dbo.表名" queryout c:\currency.txt -S 数据库实例 -U"用户" -P"密码" -c -t"," -r"\n" -k -b5000 -a65535
96
97 bcp transcend.dbo.AMQUOTEPRICE in "d:\AMQUOTEPRICE.txt" -c -t"|" -Usa -P -SsysbaseHQ -b5000
98 -b5000可以指定多少行作为一个事物提交，这样能有效减少日志的大小, 其他命令详见bcp的帮助。
99
100 在使用命令xp_cmdshell的时候需要设置权限：
101 /*MSsql2005 如何启用xp_cmdshell
102 默认情况下,sql server2005安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤
103 */
104 -- 允许配置高级选项
105 EXEC sp_configure 'show advanced options', 1
106 GO
107 -- 重新配置
108 RECONFIGURE
109 GO
110 -- 启用xp_cmdshell
111 EXEC sp_configure 'xp_cmdshell', 1
112 GO
113 --重新配置
114 RECONFIGURE
115 GO
116
117 --执行想要的xp_cmdshell语句
118 Exec xp_cmdshell 'query user'
119 GO
120
121 --用完后,要记得将xp_cmdshell禁用(出于安全考虑)
122 -- 允许配置高级选项
123 EXEC sp_configure 'show advanced options', 1
124 GO
125 -- 重新配置
126 RECONFIGURE
127 GO
128 -- 禁用xp_cmdshell
129 EXEC sp_configure 'xp_cmdshell', 0
130 GO
131 --重新配置
132 RECONFIGURE
133 GO
