Bcp 使用心得.docx
- 文档编号:12711278
- 上传时间:2023-04-21
- 格式:DOCX
- 页数:21
- 大小:230.57KB
Bcp 使用心得.docx
《Bcp 使用心得.docx》由会员分享,可在线阅读,更多相关《Bcp 使用心得.docx(21页珍藏版)》请在冰豆网上搜索。
Bcp使用心得
Bcp 使用心得
在做这方面研究的时候,的确遇到了不少麻烦。
首先在做bcp的时候,要开通大数据量访问权限
一、基于sql语句的导入导出
如果是基于SQL语句的导入导出,需要使用存储过程“master..xp_cmdshell”,默认情况下,sqlserver2005、sqlserver2008安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果未开通,会报如下错误:
(查询分析器:
使用xp_cmdshell需要开启服务器的设置且需要开通账户的权限。
这个权限DBA是不会给开启的)
消息15281,级别16,状态1,过程xp_cmdshell,第1行
SQLServer阻止了对组件'xp_cmdshell'的过程'sys.xp_cmdshell'的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。
系统管理员可以通过使用sp_configure启用'xp_cmdshell'。
有关启用'xp_cmdshell'的详细信息,请参阅SQLServer联机丛书中的"外围应用配置器"。
有两种开关方式:
i. Sql命令开启
--允许配置高级选项
EXECsp_configure'showadvancedoptions',1
GO
--重新配置
RECONFIGURE
GO
--启用xp_cmdshell
EXECsp_configure'xp_cmdshell',1
GO
--重新配置
RECONFIGURE
GO
--执行想要的xp_cmdshell语句
Execxp_cmdshell'queryuser'
GO
--用完后,要记得将xp_cmdshell禁用(出于安全考虑)
--允许配置高级选项
EXECsp_configure'showadvancedoptions',1
GO
--重新配置
RECONFIGURE
GO
--禁用xp_cmdshell
EXECsp_configure'xp_cmdshell',0
GO
--重新配置
RECONFIGURE
GO
b) 图形方式开启
1.sql2008方案、
选择数据库服务器,右键→方面→外围应用配置器→XPCmdShellEnabled,true为启用,false为禁用
1.sql2005方案
在外围应用配置器中设置,如下图
c) Sds
二、基于cmd的访问
默认情况下,只要你安装了sql或sql的客户端,直接用dos命令就可以访问bcp,如果使用bcp无法访问,说明你的环境变量path没有配置sqlserver的路径,需要加上如下路径:
C:
\ProgramFiles\MicrosoftSQLServer\100\Tools\Binn(这个是我本机bcp.exe的路径),可以依据自己的情况进行配置。
如果安装完sql的客户端,不重启机器的话,用程序调用cmd去执行bcp是无法访问的;直接在dos命令中写bcp命令,是没有问题的;目前的job就遇到此问题,如果大家有兴趣的话,可以研究下,这个目前还不太确定。
三、Bcp语法
代码:
bcp{[[database_name.][owner].]{table_name|view_name}|"query"}
{in|out|queryout|format}data_file
[-mmax_errors][-fformat_file][-eerr_file]
[-Ffirst_row][-Llast_row][-bbatch_size]
[-n][-c][-w][-N][-V(60|65|70)][-6]
[-q][-Ccode_page][-tfield_term][-rrow_term]
[-iinput_file][-ooutput_file][-apacket_size]
[-Sserver_name[\instance_name]][-Ulogin_id][-Ppassword]
[-T][-v][-R][-k][-E][-h"hint[,...n]"]
参数database_name
指定的表或视图所在数据库的名称。
如果未指定,则为用户默认数据库。
owner
表或视图所有者的名称。
如果执行大容量复制操作的用户拥有指定的表或视图,则owner是可选的。
如果没有指定owner并且执行大容量复制操作的用户不拥有指定的表或视图,则Microsoft?
SQLServer?
2000将返回错误信息并取消大容量复制操作。
table_name
是将数据复制到SQLServer时(in)的目的表名,以及从SQLServer复制数据时(out)的源表名。
view_name
是将数据复制到SQLServer时(in)的目的视图名,以及从SQLServer复制数据时(out)的源视图名。
只有其中所有列都引用同一个表的视图才能用作目的视图。
有关将数据复制到视图的限制的更多信息,请参见INSERT。
Query
是返回一个结果集的Transact-SQL查询。
如果查询返回多个结果集,例如指定COMPUTE子句的SELECT语句,只有第一个结果集将复制到数据文件,随后的结果集被忽略。
使用双引号引起查询语句,使用单引号引起查询语句中嵌入的任何内容。
在从查询中大容量复制数据时,还必须指定queryout。
in|out|queryout|format
指定大容量复制的方向。
in是从文件复制到数据库表或视图,out是指从数据库表或视图复制到文件。
只有从查询中大容量复制数据时,才必须指定queryout。
根据指定的选项(-n、-c、-w、-6或-N)以及表或视图分隔符,format将创建一个格式文件。
如果使用format,则还必须指定-f选项。
说明MicrosoftSQLServer6.5中的bcp实用工具不支持大容量复制到包含sql_variant或bigint数据类型的表。
data_file
大容量复制表或视图到磁盘(或者从磁盘复制)时所用数据文件的完整路径。
当将数据大容量复制到SQLServer时,此数据文件包含将复制到指定表或视图的数据。
当从SQLServer大容量复制数据时,该数据文件包含从表或视图复制的数据。
路径可以有1到255个字符。
-mmax_errors
指定在大容量复制操作取消之前可能产生的错误的最大数目。
bcp无法复制的每一行都将被忽略并计为一个错误。
如果没有包括该选项,则默认为10。
-fformat_file
指定格式文件的完整路径,该格式文件包含以前在同一个表或视图上使用bcp时的存储响应。
当使用由format选项所创建的格式文件大容量复制入或复制出数据时,使用此选项。
格式文件的创建是可选的。
在提示几个格式问题之后,bcp将提示是否在格式文件中保存回答。
默认文件名为Bcp.fmt。
大容量复制数据时,bcp可引用一个格式文件,因此不必重新交互输入以前的回答。
如果未使用此选项,也没有指定–n、-c、-w、-6或-N,则bcp将提示输入格式信息。
-eerr_file
指定错误文件的完整路径,此错误文件用于存储bcp无法从文件传输到数据库的所有行。
来自bcp的错误信息将发送到用户工作站。
如果未使用此选项,则不创建错误文件。
-Ffirst_row
指定要大容量复制的第一行的序数。
默认值是1,表示在指定数据文件的第一行。
-Llast_row
指定要大容量复制的最后一行的序数。
默认值是0,表示指定数据文件中的最后一行。
-bbatch_size
指定所复制的每批数据中的行数。
每个批处理作为一个事务复制至服务器。
SQLServer提交或回滚(在失败时)每个批处理的事务。
默认情况下,指定的数据文件中的所有数据都作为一批复制。
请不要与-h"ROWS_PER_BATCH=bb"选项一起使用。
-n
使用数据的本机(数据库)数据类型执行大容量复制操作。
此选项不提示输入每一字段,它将使用本机值。
-c
使用字符数据类型执行大容量复制操作。
此选项不提示输入每一字段;它使用char作为存储类型,不带前缀,\t(制表符)作为字段分隔符,\n(换行符)作为行终止符。
-w
使用Unicode字符执行大容量复制操作。
此选项不提示输入每一字段;它使用nchar作为存储类型,不带前缀,\t(制表符)作为字段分隔符,\n(换行符)作为行终止符。
不能在SQLServer6.5版或更早版本中使用。
-N
对非字符数据使用数据的本机(数据库)数据类型和对字符数据使用Unicode字符类型执行大容量复制操作。
这是可替代-w选项的性能更高的选项,其目的是使用数据文件将数据从一个SQLServer传输到另一个SQLServer中。
它不提示输入每一字段。
在需要传输包含ANSI扩展字符的数据以及想利用本机模式的性能时,可以使用这一选项。
不能在SQLServer6.5版或更早版本中使用-N选项。
-V(60|65|70)
使用SQLServer早期版本中的数据类型执行大容量复制操作。
此选项与字符(-c)或本机(-n)格式一起使用。
此选项并不提示输入每一字段,它使用默认值。
例如,若要将SQLServer6.5中的bcp实用工具所支持(但ODBC不再支持)的日期格式大容量复制到SQLServer2000,可使用-V65参数。
重要将数据从SQLServer大容量复制到数据文件时,即使指定了–V,bcp实用工具也不会为任何datetime或smalldatetime数据生成SQLServer6.0或SQLServer6.5的日期格式。
日期将始终以ODBC格式写入。
另外,由于SQLServer6.5版或更早版本不支持可为空的bit数据,因此bit列中的空值写为值0。
-6
使用SQLServer6.0或SQLServer6.5数据类型执行大容量复制操作。
仅为保持向后兼容性。
改为使用–V选项。
-q
在bcp实用工具和SQLServer实例的连接中执行SETQUOTED_IDENTIFIERSON语句。
使用该选项指定包含空格或引号的数据库、所有者、表或视图的名称。
将由三部分组成的整个表名或视图名引在双引号("")中。
-Ccode_page
仅为保持向后兼容性。
作为代替,请在格式文件或交互式bcp中为每一列指定一个排序规则名。
指定数据文件中的数据代码页。
只有当数据中包含字符值大于127或小于32的char、varchar或text列时,code_page才有用。
代码页值描述
ACPANSI/Microsoft?
(ISO1252)。
OEM客户程序使用的默认代码页。
如果未指定-C,则这是bcp使用的默认代码页。
RAW不发生从一个代码页到另一个代码页的转换。
因为不发生转换,所以这是最快的选项。
<值>特定的代码页号码,例如850。
-tfield_term
指定字段终止符。
默认的字段终止符是\t(制表符)。
使用此参数替代默认字段终止符。
-rrow_term
指定行终止符。
默认的行终止符是\n(换行符)。
使用此参数替代默认行终止符。
-iinput_file
指定响应文件的名称,使用交互模式(未指定–n、-c、-w、-6或-N)执行大容量复制时,响应文件包含对每一字段命令提示问题的响应。
-ooutput_file
指定接收bcp输出(从命令提示重定向)的文件的名称。
-apacket_size
指定发送到和发送自服务器的每个网络数据包的字节数。
可以使用SQLServer企业管理器(或sp_configure系统存储过程)设置服务器配置选项。
但是,使用此选项可以单个地替代服务器配置选项。
packet_size可以设置为4096到65535字节,默认值为4096。
数据包大小的增加能够提高大容量复制操作的性能。
如果要求一个较大的数据包而得不到,则使用默认设置。
bcp生成的性能统计显示出所使用数据包的大小。
-Sserver_name[\instance_name]
指定要连接到的SQLServer实例。
指定server_name以连接该服务器上的SQLServer默认实例。
指定server_name\instance_name以连接到该服务器上的SQLServer2000命名实例。
如果未指定服务器,则bcp连接到本地计算机上的SQLServer默认实例。
从网络上的远程计算机执行bcp时,要求此选项。
-Ulogin_id
指定用于连接到SQLServer的登录ID。
-Ppassword
指定登录ID的密码。
如果未使用此选项,则bcp将提示输入密码。
如果不带密码将此选项用于命令提示行末尾,则bcp将使用默认密码(NULL)。
-T
指定bcp使用网络用户的安全凭据,通过信任连接连接到SQLServer。
不需要login_id和password。
-v
报告bcp实用工具的版本号和版权。
-R
指定使用为客户端计算机的区域设置定义的区域格式,将货币、日期和时间数据大容量复制到SQLServer中。
默认情况下,将会忽略区域设置。
-k
指定在大容量复制操作中空列应保留一个空值,而不是对插入的列赋予默认值
-E
指定标识列的值出现在要导入的文件中。
如果没有给出-E,则正导入的数据文件中此列的标识值将被忽略,而且SQLServer2000会根据创建表期间指定的种子值和增量值自动指派唯一的值。
如果数据文件的表或视图中不包含标识列的值,则使用格式文件指定导入数据时应跳过表或视图中的标识列;SQLServer2000将自动为该列指派唯一值。
有关详细信息,请参见DBCCCHECKIDENT。
-h"hint[,...n]"
指定在大容量复制数据到表或视图时所使用的提示。
在大容量复制数据到SQLServer6.x或更早版本时,不能使用此选项。
提示描述
ORDER(column[ASC|DESC][,...n])数据文件中数据的排序次序。
如果要装载的数据已根据表中的聚集索引排序,则会提高大容量复制的性能。
如果数据文件按不同次序排序,或者该表没有聚集索引,则将忽略ORDER提示。
所提供的列名必须是目的表中的有效列。
默认情况下,bcp假设数据文件没有排序。
ROWS_PER_BATCH=bb每批中数据的行数(即bb)。
在未指定-b时使用,这将使整个数据文件作为单个事务发送到服务器。
服务器根据值bb优化大容量装载。
默认情况下,ROWS_PER_BATCH未知。
KILOBYTES_PER_BATCH=cc每批中数据的千字节(KB)近似数量(即cc)。
默认情况下,KILOBYTES_PER_BATCH未知。
TABLOCK大容量复制操作期间将获取表级锁。
由于只在大容量复制操作期间才控制锁减少了表中锁的争夺,因此此提示可以显著提高性能。
如果表没有索引并且指定了TABLOCK,则该表可以同时由多个客户端装载。
默认情况下,锁定行为是由表选项tablelockonbulkload决定的。
CHECK_CONSTRAINTS大容量复制操作期间,将检查目的表上的所有约束。
默认情况下,将会忽略约束。
FIRE_TRIGGERS与in参数一起指定,在目的表上定义的任何插入触发器将在大容量复制操作期间执行。
如果没有指定FIRE_TRIGGERS,则不执行插入触发器。
对于out、queryout和format参数,将忽略FIRE_TRIGGERS。
注释
将忽略要导入的数据文件中计算列或timestamp列的值,SQLServer2000自动赋值。
如果数据文件不包含表中的计算列或timestamp列的值,可用格式文件指定应在导入数据时跳过表中的计算列和timestamp列;SQLServer将自动为该列赋值。
计算列和timestamp列照常会从SQLServer大容量复制到一个数据文件。
SQLServer标识符(包括数据库名称、表名或视图名、登录和密码)可以包含诸如嵌入空格和引号等字符。
当在命令提示符处指定包含空格或引号的标识符或文件名时,需要将该标识符引在双引号("")内。
另外,对于包含嵌入空格或引号的所有者、表或视图的名称,可以指定-q选项,或者将所有者、表或视图的名称在双引号内用方括号([])括起来。
四、简单示例:
导出:
导出整张表:
bcpmaster.dbo.spt_valuesoutE:
\b.txt-S".\SQLEXPRESS"-U"sa"-P"123456"-c
bcpmaster.dbo.spt_valuesoutE:
\b.txt-S".\SQLEXPRESS"-T–c
bcpmaster.dbo.spt_valuesout E:
\a.dat-S".\SQLEXPRESS"-U"sa"-P"123456"-t","-c
依据查询整张表:
bcp"SELECT*FROMmaster.dbo.spt_values"queryoutE:
\b.txt-S".\SQLEXPRESS"-U"sa"-P"123456"-c
bcp"SELECT*FROMmaster.dbo.spt_values"queryoutE:
\b.txt-S".\SQLEXPRESS"-T-c
导入:
普通导入:
bcpmaster.dbo.spt_valuesinE:
\b.txt-S".\SQLEXPRESS"-U"sa"-P"123456"-c
格式化导入:
BcpBI.dbo.tabinE:
\BI固化数据\a.dat-fE:
\a.fmt-S".\SQLEXPRESS"-U"sa"-P"123456"-t","
格式化:
Fmt文件
bcpSCM.dbo.tabformatnul -fE:
\BI固化数据\a.fmt-S"PEK7-6TKX23X\SQLEXPRESS"-U"sa"-P"123456"-c-t","
XML文件
bcpSCM.dbo.tabformatnul -x-fE:
\BI固化数据\t.xml-S"PEK7-6TKX23X\SQLEXPRESS"-U"sa"-P"123456"-c-t","
查询分析器导入导出:
导出
EXECmaster..xp_cmdshell'bcpCaching_Test..v_v_v_Vout D:
\Website\BI固化数据\2013\e.txt -S"PEK7-6TKX23X\SQLEXPRESS"-U"sa"-P"qjx@"-c-k-e-x'
导入
EXECmaster..xp_cmdshell'BCPCaching_Test.dbo.vd_Day_Platform_Distinguish_BrandinD:
\Website\V+平台的数据区分品牌按日\2013\b.txt-S"PEK7-6TKX23X\SQLEXPRESS"-U"sa"-P"qjx@"-c'
/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:
\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:
\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:
\test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:
\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
/********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:
\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
五、 使用格式化文件跳过表列(SQLServer)
使用非XML格式化文件
可以修改非XML格式化文件以跳过某个表列。
此操作通常是使用 bcp 实用工具创建一个默认非XML格式化文件,并在文本编辑器中修改此默认文件。
修改过的格式化文件必须将每个现有字段映射到相应的表列并指明要跳过哪个或哪些表列。
修改默认非XML数据文件的方
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Bcp 使用心得 使用 心得