第07章 数据的导入和导出

1、第7章第7章 数据的导入和导出 大多应用程序都设计成允许用户操作单独的数据。但实际应用中经常都需要导入或导出大量数据。 导入大量数据时，使用INSERT语句的执行效率不是特别好。 导出数据时，若向应用程序返回一个结果集，再由应用程序将行写入文件或其他目的地，效率也不高。 SQL Server专门为这些操作提供了新的功能，允许高效地导入和导出大量数据，同时将资源的消耗降至最低。第第7章章 数据的导入和导出数据的导入和导出两年前，我合作过的一个企业有一个完整的分公司专门负责为合作伙伴履行订单。合作伙伴每天都将订单文件上传到FTP服务器。这些文件会被解析并加载到数据库，然后通过一系列出货、打包、发货

2、和结帐过程进行处理。遗憾的是，导入每个合作伙伴的订单往往都要花上2-7个小时。但为了满足和客户的协议，要求所有合作伙伴的文件都必须在1小时内导入完成。一个文件夹中往往会有30或更多的文件等待处理。除此之外，只有5%的合作伙伴才允许批量上传订单，因为系统处理不了任何额外的负载。用于导入订单的系统由十几C+应用程序构成，30多个文件夹散布在3台服务器上，在导入订单的数据库中，只存在有少量代码。这些程序是十几年前写的，它们90%惟上的工作都是在不同的目录之间移动文件，而目录唯一的作用就是在处理期间隔离文件。通过进一步研究，我们还发现对多个应用程序进行管理的代码试图访问相同的文件。这所以出现这个现象，

3、完全是整个系统的组合方式造成的。文件终于各就各位并开始真正进行和业务有关的处理时，我们发现应用程序每次从文件中读取一行并处理它。针对处理的每一行，应用程序都执行超过100个查询来校验产品代码、现有库存、价格水平以及其他几个业务规则。我们重写整个系统来利用SQL Server的批量导入功能。在重写的阶段1，我们删除了所有的C+代码和整个文件夹结构，用单个文件夹来取而代之，用一个存储过程对文件进行BCP，并用另一个存储过程处理文件导入之后的一切事情。在阶段2，我们将BCP例程替换成一个SSIS包，它能并行处理多个文件，而且在处理多种数据格式时更灵活。完成阶段1后，导入例程的效率已远远地超出合作伙伴

4、上传文件的速度。文件上传之后不到一分钟的时间，所有数据便已导入并处理完成，订单在仓库那里排队等待打包。完成阶段2后，我们将订单上传服务扩展到其他95%的合作伙伴。甚至最大的合作伙伴的订单文件也能在文件上传之后的15秒内处理完毕。系统升级改造的直接结果是有利于扩大客户群。在新系统改造完毕之后的半年之内，公司的业务增长超过400%以上。实例回放实例回放1. 导入和导出数据学习目标 使用BCP(bulk copy program)将数据导出到文件 使用BCP从文件导入数据 使用BULK INSERT从文件导入数据 使用“SQL Server导入导出向导”进行数据导入和导出1. 导入和导出数据批量复制

5、程序（BCP） BCP是将文件中的具有一定格式的数据导入SQL Server以及将表导出到文件最有效的方式。 BCP设计成导入和导出数据的一种非常快的、轻量级的解决方案。 若要在导入/导出过程中执行转换，或者执行错误处理例程，那么应该使用SQLServer Integration Services(SSIS)来进行导入/导出。 使用BCP导出数据，运行BCP的帐户只需要表或视图上的SELECT权限。导入数据，需要SELECT, INSERT和ALTER TABLE权限。1. 导入和导出数据BCP是从命令行执行的实用程序，语法如下：bcp database_name.owner.table_na

6、me | 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 (60 | 65 | 70) -6 -q -C code_page -t field_term -r row_term -i input_file -o output_file -a packet_size -S server_nameinstance_name -U login_id

7、 -P password -T -v -R -k -E -h hint ,.n 警告：大小写敏感SQL Server数据导入导出工具数据导入导出工具BCP详解详解http:/ 导入和导出数据示例bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt n SHOTEK Tbcp AdventureWorks.HumanResources.Department in c:testdepartment.txt c SHOTEK U -Pbcp “SELECT Name, GroupName FROM HumanRes

8、ources.Department” queryout c:testdepartment.txt n SHOTEK -T说明：-n c参数互斥-n 使用数据的本机（数据库）数据类型执行大容量复制操作。此选项不提示输入每一字段，它将使用本机值。-c 使用字符数据类型执行大容量复制操作。此选项不提示输入每一字段；它使用 char 作为存储类型，不带前缀，t（制表符）作为字段分隔符，n（换行符）作为行终止符。-S 开关指定要连接到的实例名称。-T 开关指定一个可信的连接，将用执行BCP时使用的Windows账户建立连接。-U P 可使用SQL Server登录凭据。1. 导入和导出数据注意强制检查约

9、束和触发器强制检查约束和触发器用BCP将数据导入表时，触发器和检查约束默认禁用。如果想强制检查约束，并在导入期间激活触发器，需要使用-h开关。如果导入时不禁用触发器和检查约束，就不需要ALTER TABLE权限。1. 导入和导出数据BULK INSERT命令BCP实用程序的缺点在于它是一个命令行程序。BULK INSERT命令提供了许多和BCP相同的选项，行为也基本相同，但存在以下2个区别： BULK INSERT不能导出数据 BULK INSERT是T-SQL命令，不需要指定实例名或登录凭据。BULK INSERT 详解http:/ 导入和导出数据SQL Server导入和导出向导n BCP

10、和BULK INSERT命令提供了简单 、轻量级的方式，通过使用文件来导入和导出数据。n 若想直接在来源和目的地之间导入和导出数据，同时应用转换和错误处理例程，则可以使用SQL Server Intergration Service(SSIS)的功能来构建。n SSMS中的“SQL Server导入和导出向导”提供了SSIS功能的一个子集，允许管理员在来源和目的地之间移动数据。n 向导使用SSIS能识别的任何数据源，包括EXCEL，ACCESS或者XML文件。n 向导支持OLEDB数据提供程序的任何数据源或目的地。n 向导不要求数据源或目的地是SQLServer实例。n 向导可从多个表或文件移

11、动数据；BCP和BULK INSERT只能操作单个表、视图或查询。实训1 使用BCP导入和导出数据1.打开命令提示符窗口执行以下命令bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt c S -T2.在记事本中打开department.txt文件查看文件内容3.执行以下命令bcp AdventureWorks.HumanResources.Department out c:testdepartment.txt n S -T4.查看导出文件内容。实训2 练习使用向导导入和导出数据将AdventureWorks数据库中的数据导出至一个EXCEL文件中本章回顾n BCP程序允许将数据从文件导入表，或者将数据从表导出到文件。n BULK INSERT是一个T-SQL命令，允许将数据从文件导入表。n “导入和导出向导”使用SSIS的一个子集在数