方法一用BCP命令
bcp 实用工具
bcp 实用工具在 Microsoft? SQL Server? 实例和数据文件之间以用户指定的格式复数据
语法
bcp {[[database_name][owner]]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[m max_errors] [f format_file] [e err_file]
[F first_row] [L last_row] [b batch_size]
[n] [c] [w] [N] [V ( | | )] []
[q] [C code_page] [t field_term] [r row_term]
[i input_file] [o output_file] [a packet_size]
[S server_name[instance_name]] [U login_id] [P password]
[T] [v] [R] [k] [E] [h "hint [n]"]
参数database_name
指定的表或视图所在数据库的名称如果未指定则为用户默认数据库
owner
表或视图所有者的名称如果执行大容量复制操作的用户拥有指定的表或视图则 owner 是可选的如果没有指定 owner 并且执行大容量复制操作的用户不拥有指定的表或视图则 Microsoft? SQL Server? 将返回错误信息并取消大容量复制操作
table_name
是将数据复制到 SQL Server 时 (in) 的目的表名以及从 SQL Server 复制数据时 (out) 的源表名
view_name
是将数据复制到 SQL Server 时 (in) 的目的视图名以及从 SQL Server 复制数据时 (out) 的源视图名只有其中所有列都引用同一个表的视图才能用作目的视图有关将数据复制到视图的限制的更多信息请参见 INSERT
Query
是返回一个结果集的 TransactSQL 查询如果查询返回多个结果集例如指定 COMPUTE 子句的 SELECT 语句只有第一个结果集将复制到数据文件随后的结果集被忽略使用双引号引起查询语句使用单引号引起查询语句中嵌入的任何内容在从查询中大容量复制数据时还必须指定 queryout
in | out | queryout | format
指定大容量复制的方向in 是从文件复制到数据库表或视图out 是指从数据库表或视图复制到文件只有从查询中大容量复制数据时才必须指定 queryout根据指定的选项(ncw 或 N)以及表或视图分隔符format 将创建一个格式文件如果使用 format则还必须指定 f 选项
说明 Microsoft SQL Server 中的 bcp 实用工具不支持大容量复制到包含 sql_variant 或 bigint 数据类型的表
data_file
大容量复制表或视图到磁盘(或者从磁盘复制)时所用数据文件的完整路径当将数据大容量复制到 SQL Server 时此数据文件包含将复制到指定表或视图的数据当从 SQL Server 大容量复制数据时该数据文件包含从表或视图复制的数据路径可以有 到 个字符
m max_errors
指定在大容量复制操作取消之前可能产生的错误的最大数目bcp 无法复制的每一行都将被忽略并计为一个错误如果没有包括该选项则默认为
f format_file
指定格式文件的完整路径该格式文件包含以前在同一个表或视图上使用 bcp 时的存储响应当使用由 format 选项所创建的格式文件大容量复制入或复制出数据时使用此选项格式文件的创建是可选的在提示几个格式问题之后bcp 将提示是否在格式文件中保存回答默认文件名为 Bcpfmt大容量复制数据时bcp 可引用一个格式文件因此不必重新交互输入以前的回答如果未使用此选项也没有指定 –ncw 或 N则 bcp 将提示输入格式信息
e err_file
指定错误文件的完整路径此错误文件用于存储 bcp 无法从文件传输到数据库的所有行来自 bcp 的错误信息将发送到用户工作站如果未使用此选项则不创建错误文件
F first_row
指定要大容量复制的第一行的序数默认值是 表示在指定数据文件的第一行
L last_row
指定要大容量复制的最后一行的序数默认值是 表示指定数据文件中的最后一行
b batch_size
指定所复制的每批数据中的行数每个批处理作为一个事务复制至服务器SQL Server 提交或回滚(在失败时)每个批处理的事务默认情况下指定的数据文件中的所有数据都作为一批复制请不要与 h "ROWS_PER_BATCH = bb" 选项一起使用
n
使用数据的本机(数据库)数据类型执行大容量复制操作此选项不提示输入每一字段它将使用本机值
c
使用字符数据类型执行大容量复制操作此选项不提示输入每一字段;它使用 char 作为存储类型不带前缀t(制表符)作为字段分隔符n(换行符)作为行终止符
w
使用 Unicode 字符执行大容量复制操作此选项不提示输入每一字段;它使用 nchar 作为存储类型不带前缀t(制表符)作为字段分隔符n(换行符)作为行终止符不能在 SQL Server 版或更早版本中使用
N
对非字符数据使用数据的本机(数据库)数据类型和对字符数据使用 Unicode 字符类型执行大容量复制操作这是可替代 w 选项的性能更高的选项其目的是使用数据文件将数据从一个 SQL Server 传输到另一个 SQL Server 中它不提示输入每一字段在需要传输包含 ANSI 扩展字符的数据以及想利用本机模式的性能时可以使用这一选项不能在 SQL Server 版或更早版本中使用 N 选项
V ( | | )
使用 SQL Server 早期版本中的数据类型执行大容量复制操作此选项与字符 (c) 或本机 (n) 格式一起使用此选项并不提示输入每一字段它使用默认值例如若要将 SQL Server 中的 bcp 实用工具所支持(但 ODBC 不再支持)的日期格式大容量复制到 SQL Server 可使用 V 参数
重要 将数据从 SQL Server 大容量复制到数据文件时即使指定了 –Vbcp 实用工具也不会为任何 datetime 或 smalldatetime 数据生成 SQL Server 或 SQL Server 的日期格式日期将始终以 ODBC 格式写入另外由于 SQL Server 版或更早版本不支持可为空的 bit 数据因此 bit 列中的空值写为值
使用 SQL Server 或 SQL Server 数据类型执行大容量复制操作仅为保持向后兼容性改为使用 –V 选项
q
在 bcp 实用工具和 SQL Server 实例的连接中执行 SET QUOTED_IDENTIFIERS ON 语句使用该选项指定包含空格或引号的数据库所有者表或视图的名称将由三部分组成的整个表名或视图名引在双引号 (" ") 中
C code_page
仅为保持向后兼容性作为代替请在格式文件或交互式 bcp 中为每一列指定一个排序规则名
指定数据文件中的数据代码页只有当数据中包含字符值大于 或小于 的 charvarchar 或 text 列时code_page 才有用
代码页值 描述
ACP ANSI/Microsoft Windows? (ISO )
OEM 客户程序使用的默认代码页如果未指定 C则这是 bcp 使用的默认代码页
RAW 不发生从一个代码页到另一个代码页的转换因为不发生转换所以这是最快的选项
<值> 特定的代码页号码例如
t field_term
指定字段终止符默认的字段终止符是 t(制表符)使用此参数替代默认字段终止符
r row_term
指定行终止符默认的行终止符是 n(换行符)使用此参数替代默认行终止符
i input_file
指定响应文件的名称使用交互模式(未指定 –ncw 或 N)执行大容量复制时响应文件包含对每一字段命令提示问题的响应
o output_file
指定接收 bcp 输出(从命令提示重定向)的文件的名称
a packet_size
指定发送到和发送自服务器的每个网络数据包的字节数可以使用 SQL Server 企业管理器(或 sp_configure 系统存储过程)设置服务器配置选项但是使用此选项可以单个地替代服务器配置选项packet_size 可以设置为 到 字节默认值为
数据包大小的增加能够提高大容量复制操作的性能如果要求一个较大的数据包而得不到则使用默认设置bcp 生成的性能统计显示出所使用数据包的大小
S server_name[instance_name]
指定要连接到的 SQL Server 实例指定 server_name 以连接该服务器上的 SQL Server 默认实例指定 server_nameinstance_name 以连接到该服务器上的 SQL Server 命名实例如果未指定服务器则 bcp 连接到本地计算机上的 SQL Server 默认实例从网络上的远程计算机执行 bcp 时要求此选项
U login_id
指定用于连接到 SQL Server 的登录 ID
P password
指定登录 ID 的密码如果未使用此选项则 bcp 将提示输入密码如果不带密码将此选项用于命令提示行末尾则 bcp 将使用默认密码 (NULL)
T
指定 bcp 使用网络用户的安全凭据通过信任连接连接到 SQL Server不需要 login_id 和 password
v
报告 bcp 实用工具的版本号和版权
R
指定使用为客户端计算机的区域设置定义的区域格式将货币日期和时间数据大容量复制到 SQL Server 中默认情况下将会忽略区域设置
k
指定在大容量复制操作中空列应保留一个空值而不是对插入的列赋予默认值
E
指定标识列的值出现在要导入的文件中如果没有给出 E则正导入的数据文件中此列的标识值将被忽略而且 SQL Server 会根据创建表期间指定的种子值和增量值自动指派唯一的值如果数据文件的表或视图中不包含标识列的值则使用格式文件指定导入数据时应跳过表或视图中的标识列;SQL Server 将自动为该列指派唯一值有关详细信息请参见 DBCC CHECKIDENT
h "hint [n]"
指定在大容量复制数据到表或视图时所使用的提示在大容量复制数据到 SQL Server 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则该表可以同时由多个客户端装载默认情况下锁定行为是由表选项 table lock on bulk load 决定的
CHECK_CONSTRAINTS 大容量复制操作期间将检查目的表上的所有约束默认情况下将会忽略约束
FIRE_TRIGGERS 与 in 参数一起指定在目的表上定义的任何插入触发器将在大容量复制操作期间执行如果没有指定 FIRE_TRIGGERS则不执行插入触发器对于 outqueryout 和 format 参数将忽略 FIRE_TRIGGERS
注释
将忽略要导入的数据文件中计算列或 timestamp 列的值SQL Server 自动赋值如果数据文件不包含表中的计算列或 timestamp 列的值可用格式文件指定应在导入数据时跳过表中的计算列和 timestamp 列;SQL Server 将自动为该列赋值
计算列和 timestamp 列照常会从 SQL Server 大容量复制到一个数据文件
SQL Server 标识符(包括数据库名称表名或视图名登录和密码)可以包含诸如嵌入空格和引号等字符当在命令提示符处指定包含空格或引号的标识符或文件名时需要将该标识符引在双引号(" ") 内另外对于包含嵌入空格或引号的所有者表或视图的名称可以指定 q 选项或者将所有者表或视图的名称在双引号内用方括号 ([ ]) 括起来
例如Northwind 数据库内有表 Janes Orders该表由用户 Jane Doe 所拥有若要使用登录 Jane Doe 和密码 go dba 将该表从 Northwind 数据库大容量复制到 Orderstxt 文件请执行下列命令之一
bcp "NorthwindJane DoeJanes Orders" out "Janes Orderstxt" c q U"Jane Doe" P"go dba"
bcp "Northwind[Jane Doe][Janes Orders]" out "Janes Orderstxt" c U"Jane Doe" P"go dba"
若要指定包含空格或引号的数据库名称必须使用 q 选项
有关此实用工具位置和运行方式的信息请参见命令提示实用工具入门
请参见
在不同排序规则间复制数据
使用 bcp 和 BULK INSERT
并行数据装载
SET QUOTED_IDENTIFIER
sp_tableoption
使用格式文件
方法二DTS 导入/导出向导
DTS 导入/导出向导
在所有的数据转换服务 (DTS) 工具中DTS 导入/导出向导为在 OLE DB 数据源之间复制数据提供了最简单的方法
在连接到源和目的之后可以选择要导入或导出的数据并可对要复制的数据应用各种转换(例如通过选择列或使用 Microsoft ActiveX脚本)在大多数情况下可以自动与源数据一起复制主键和外键约束
说明 可以复制从 SQL 查询中获得的数据SQL 查询可以包含来自同一数据库或分布式查询中的多个表的联接作为整个进程的一部分如果不存在任何表则 DTS 导入/导出向导会自动为您创建目的表
可用的数据源
使用 DTS 导入/导出向导可以连接到下列数据源
大多数的 OLE DB 和 ODBC 数据源以及用户指定的 OLE DB 数据源
文本文件
到一个或多个 Microsoft SQL Server实例的其它连接
Oracle 和 Informix 数据库
必须已经安装 Oracle 或 Informix 客户端软件
Microsoft Excel 电子表格
Microsoft Access 和 Microsoft FoxPro数据库
dBase 或 Paradox 数据库
有关更多信息请参见 DTS 连接
转换数据
除复制数据外还可以用诸如 Microsoft Visual Basic? Scripting Edition (VBScript) 或 Microsoft JScript? 之类的 ActiveX 脚本语言转换列级数据有关更多信息请参见 DTS 转换转换数据任务和在 DTS 中使用 ActiveX 脚本
复制数据库对象
使用 DTS 导入/导出向导可以转换索引视图角色存储过程和引用完整性约束等数据库对象有关更多信息请参见复制 SQL Server 对象任务
保存 DTS 包
完成 DTS 导入/导出向导之后可以将连接转换以及调度信息保存为 DTS 包可以下列形式保存包
保存到 SQL Server msdb 数据库
保存到 SQL Server Meta Data Services
保存存为结构化存储文件(dts 文件)
保存为 Visual Basic 文件
可以立即运行包或调度该包以供今后执行
有关更多信息请参见保存 DTS 包和调度 DTS 包的执行
编辑包
如果使用 DTS 导入/导出向导创建了一个包然后保存了该包则可以在 DTS 设计器中编辑这个包使用 DTS 设计器可以自定义在 DTS 导入/导出向导中创建的基本包添加步骤任务转换事件驱动逻辑以及配置工作流有关更多信息请参见 DTS 设计器
