SQL批量复制命令的六个陷阱

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:
批量复制工具(BCP)是 SQL Server主要的命令行工具之一,使用非常方便,它也是SQL Server导入导出海量数据的方式。但是DBA应注意BCP存在几项限制,本文作者通过自身经历总结了一些主要的问题表现。

  1、没有对UTF-8的支持

   SQL Server有对Unicode的本地支持,使用过nvarchar和ntext字段类型的任何人都知道。它通过映射每个字符为双字节实体来内部处理 Unicode。如果你只是处理SQL Server实例之间的数据,那么不会有任何问题,因为它们都以相同的方式存储。

  不过,如果你 试图使用BCP从把Unicode导出为UTF-8的数据来源导入数据,那事情就有点复杂了。UTF-8是Unicode的一种子变体,专门设计支持与八 位ASCII文本的向后兼容,所以默认使用八位ASCII编码的网页、电子邮件和其它格式可以用于存储Unicode数据。

  如果你从UTF-8源导出数据,不要指望对这些数据使用BCP;它一直不支持UTF-8。你必须考虑数据问题,以完整双字节Unicode导出使数据形成可接受格式。具有讽刺意味的是,另一个普通的编码可以通过“-C”开关(ISO 1252,ANSI/微软公司Windows)被BCP接受。不过,就整体而言,你最好把数据导出为双字节Unicode,以保持对BCP的最大兼容性,尤其是如果你处理的数据可能包含与ASCII不兼容的字符。

  2、注意导出的行顺序

   使用BCP通过查询导出的数据对于导出顺序遵守相同的规则,会应用于任何其它情况的查询。换句话说,如果你的查询没有明确的“ORDER BY”从句,你获得的数据看起来就是完全任意的顺序。它通常是基于隐含索引中的顺序形成的,但是我已经学会甚至连经验法则也不相信了——尤其是如果该查询 在多个表之间执行“JOIN”或者一些其它聚合函数。

  数据是按什么顺序导出的通常并不重要,但是数据以什么顺序导入是非常关键的。如果你使用的数据库是后来导入行的正确性决定于早先存在的行,而且你是批量导入数据的话,那么导出的顺序就很重要,你需要相应地建立你的BCP语句。这一点似乎显而易见,但是我经常惊讶有那么多人,甚至包括一些资深的SQL Server专家都没有意识到这一点。

  3、从BCP激活的存储过程不能接收参数

  如果你使用带有参数的存储过程,作为BCP动作Transact-SQL(T-SQL)语句的一部分,几乎可以肯定它不能用,而且会在命令行抛出函数顺序错误。

  当T-SQL语句传递给BCP时,它将被使用“SET FMTONLY ON”机制进行分析,来判断结果集的柱状格式。这意味着动态构造语句(比如带参数的存储过程)将不能正确分析,而且也不能在BCP下编译。

  如果你想解决这个问题,有几种方法可以选择:

  创建不带任何参数的存储过程,用问号激活存储过程并传入需要的参数(可能通过数据源而不是命令行接收参数)。

  用sqlcmd替代BCP。

   MSDN博客中提到了一个处理技巧,需要使用称为“openrowset”的技巧。如果你通过“OPENROWSET ”函数运行“SELECT”,你可以以临时方式传递一个T-SQL语句,从而解决调用带参数存储过程的限制。然而,这种处理技巧也有局限:例如,与语句连 接时不应该使用,因为运行会对数据库造成消极变化,而且该语句可能需要运行不止一次。

  4、导入时要注意表定义

  当你使用BCP从一个SQL Server源导出数据,并导入到另一个SQL Server时,你导出时的列定义和导入时的列定义必须相匹配。这也包括诸如NULL或者NOT NULL这类定义,在目标表缺少它们会引起静默数据损坏。

  5、在目标数据库上的触发器不能被BCP触发

  不管什么时候运行导入操作,BCP的本地行为在目标数据库上都会禁用触发器。因为BCP导入操作通常很大,如果按默认启用触发器的话,导入操作会很混乱。因此,你需要在BCP上使用命令选项“-h FIRE_TRIGGERS”,这样触发器才会被触发。

   要注意,当选项启用时,触发器会为每个批量操作运行一次,——也就是说,每次你运行BCP时执行一次。另外还要注意,在SQL Server 2005和以后的版本中,触发器使用了“行版本”,在导入操作时用tempdb来存储行版本信息。如果你的tempdb不能容纳触发器生成的大量数据涌 入,该操作将异常终止。

  6、BCP不能给本地附加文件输出

  如果你使用BCP导出数据到文件,该文件必须是新创建的。你不能选择现存文件,并把导出结果追加到文件。幸运的是,解决办法并不困难,您可以简单地导出到任何多个文件,然后使用COPY命令来整合这些结果。命令如下:

  COPY export1.dat + export2.dat export.dat








====================================分割线================================



最新内容请见作者的GitHub页:http://qaseven.github.io/

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
12月前
|
SQL 安全 网络协议
命令执行/SQL盲注无回显外带方式
命令执行/SQL盲注无回显外带方式
243 0
|
4月前
|
SQL 关系型数据库 MySQL
SQL命令
【5月更文挑战第21天】SQL命令
39 3
|
2月前
|
存储 关系型数据库 MySQL
(十五)MySQL命令大全:以后再也不用担心忘记SQL该怎么写啦~
相信大家在编写SQL时一定有一个困扰,就是明明记得数据库中有个命令/函数,可以实现自己需要的功能,但偏偏不记得哪个命令该怎么写了,这时只能靠盲目的去百度,以此来寻找自己需要的命令。
99 28
|
2月前
|
SQL 数据库 索引
SQL 命令
【7月更文挑战第10天】SQL 命令。
23 3
|
4月前
|
SQL 关系型数据库 MySQL
sql数据库命令
SQL(Structured Query Language,结构化查询语言)是用于管理关系数据库的标准编程语言。以下是一些常用的 SQL 数据库命令: 1. **创建数据库**: ``
43 7
|
3月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错合集之在本地执行代码没有问题,但是在线执行sql命令就会报错,该怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
114 0
|
4月前
|
机器学习/深度学习
T-sql 各种查询命令
T-sql 各种查询命令
|
4月前
|
SQL XML 数据库
sql导入数据库命令
在SQL Server中,数据库导入可通过多种方式实现:1) 使用SSMS的“导入数据”向导从各种源(如Excel、CSV)导入;2) BULK INSERT语句适用于导入文本文件;3) bcp命令行工具进行批量数据交换;4) OPENROWSET函数直接从外部数据源(如Excel)插入数据。在操作前,请记得备份数据库,并可能需对数据进行预处理以符合SQL Server要求。注意不同方法可能依版本和配置而异。
|
4月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令:你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”,用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示,控制ECHO和FEEDBACK开关以定制反馈,以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令,能提升工作效率和体验,是数据库管理员和开发者的必备工具。
|
4月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的HELP命令:你的数据库“百事通”
【4月更文挑战第19天】`Oracle SQL*Plus` 的 `HELP` 命令是数据库查询的强大工具,犹如“百事通”。在遇到困惑时,`HELP` 可提供详细命令解释和用法示例,解答基础到高级的 SQL 和 PL/SQL 疑问。它还是“活字典”,揭示命令关联与区别,如 `SET` 和 `ALTER SESSION`。此外,`HELP` 解释数据库概念,如“事务”,并支持模糊查询。无论新手还是专家,`HELP` 都是数据库探索的得力助手。
下一篇
云函数