【IT168 技术分析】在向2009年告别之际，我们来回顾一下过去的一年中最受欢迎的SQL Server技巧，包括了OPENROWSET、FILESTREAM等函数的用法、密码工具介绍以及DBA日常工作建议等内容。

　　通过对这些精华文章的再次回顾，希望可以帮助您梳理一下这一年以来的工作以及学习心得，对未来一年更进一步打下更坚实的基础。

　　微软SQL Server中的批量复制程序(Bulk Copy Program，BCP)能让数据库管理员将数据批量导入表中或将数据从表中批量导入文档中。它还支持一些定义数据如何导出、导入到什么地方、加载哪些数据等选项。

　　本技巧讨论一些用bcp命令批量复制数据迁入或迁出SQL Server表的示例。这些示例在SQL Server 2005和SQL Server 2008上已经测试过。并且我还用了AdventureWorks样本数据库。

　　用bcp工具导入数据

　　一个最简单的操作就是你可以用bcp工具将数据从SQL Server表bulk-copy到文本文件。在 Windows命令提示符中插入命令，你就可以运行bcp命令了。例如以下命令，从 AdventureWorks数据库里的Sales.vSalesPerson视图复制数据到C:\Data\SalesPerson.txt文件：

　　如同你看到的一样，bcp命令以工具名称开头，后面为完全合格表名database.schema.table。接下来就是out 关键字，关键字告诉bcp工具数据将会从该表中导出。目标文本文件的路径和文件名称紧跟out 关键字之后。注意本文中列出的命令例子可能包括很多行，但是所有的例子应该像一个单独的命令一样运行。

　　除了这些基本参数，bcp工具还支持控制工具行为的switch。在以上例子中，无论数据是以何种方式存储在源表中的，-c switch表示所有的数据都应是字符数据。如果你没有指定-c开关或其他相关类型的switch，你就需要在进入bcp命令后指定每个列的switch类型。

　　上述例子中另一个switch就是-T，它主要是告诉bcp工具使用可靠连接来关联SQL Server示例。如果你没有指定-T，你就必须提供用户名(-U switch)和密码(-P switch)，或者你需要提供相关信息。

　　因为在先前列举的例子中没有指定实例，bcp工具就在本地机上使用的默认实例。要指定一个SQL Server实例，就要用到-S switch，后面紧跟的是服务器名称，如下所示：

　　Bcp工具现在和Server01上的默认实例连接。如果你想连接到具体实例而不是默认的实例，你就必须指定实例名称和服务器名称，如Server01\SqlSrv。 通过默认，bcp工具使用制表符分隔目标文件中里的域。但你也可以用-t switch调过这一操作，如下：

　　在这种情况下，-t switch后有一个逗号，意思就是说数据域现在由逗号分开。这样做可让你讲数据保存到.csv 文件，便于你在Microsoft Excel文件中查看这些数据。 以上一些例子只限于将数据从表中导出。但是你还可以用bcp命令运行Transact-SQL查询、到出查询条件。例如以下bcp命令，包括只从vSalesPerson视图中检索SalesPersonID、FirstName和LastName的SELECT语句：

　　这种情况下引号里的查询通过的是bcp命令而不是表名称。此外，queryout取代了out关键字。但是命令其他部分和先前的例子相同。结果，SalesPerson.csv文件现在只包含三个指定列。你还可以让查询更加精炼：例如你可以包括限定只从源表中返回那些行的WHERE子句。

　　用bcp工具导出数据

　　Bcp工具使得导入数据和导出一样简单。要在这部分里运行这个示例，首先就要执行下面的T-SQL脚本，在AdventureWorks数据库里创建SalesPeople表：

 　　要导出数据，你需要一个源文件从中复制数据。例如下面的命令就用了最近创建的SalesPerson.csv文件加载数据到SalesPeople表：

　　首先，你必须指定目标表，这种情况下紧跟其后的为代替out或queryout的in关键字。其次，你必须指定源文件的路径和文件名称，后跟任意可用的switch。在运行这一命令并查看结果时，要注意源文件如果包括售货员的ID，这些值就不能插入到SalesPersonID列。该列定义为IDENTITY列，这样我们就可以忽视源数据。要保留

　　原始值，你就必须在命令里增加-E switch，如下面的例子中所示：

　　现在表中就包含了你想要得到的数据。

　　使用格式文件

　　在导入或导出数据时，你会发现源数据架构和目标数据架构不匹配。例如，文本文件中的这些列可能和目标表中的列的顺序不一致，或者说这些列的多少还不一样。你可以通过创建格式文件映射源文件和目标架构解决这个问题。我们通过以下示例看看它是如何工作的：

　　假如你使用了以下命令从 vSalesPerson视图中导出数据到SalesPeople.txt文件：

　　该命令使用了先前例子中同样的参数。但是注意这些列从视图中检索的顺序：LastName、FirstName、最后是SalesPersonID。 现在假设你打算使用该文件导入数据到SalesPeople表。SalesPeople表中列的顺序和文本文件中列顺序不同。要解决这个问题，你可以创建一个格式文件将这些列从源文件映射到目的文件。以下命令说明如何创建一个格式文件：

　　先前的例子表明，命令首先指定了目标表。而这一次表明后面为关键字format nul，表示bcp工具应该创建格式文件。-f参数用于指定格式文件的路径和文件名称，其后为switch。最后在你运行该命令时，就生成了包括SalesPeople表架构的格式文本。

　　下面的数据显示上述命令生成的SalesPeople.fmt格式文本内容：

　　文件(10.0)第一行确定了目前使用的bcp版本。第二行(3) 确定了表中的列数，接下来的三行为列的相关信息：

　　·第一个域为列在源文件中出现的顺序。

　　·第二个域显示每个列的源文件数据类型。因为在生成文件时指定了–c switch，在从数据文件中提取时，所有的域都用字符型数据类型。插入数据时，SQL Server会将数据转换到正确的类型。

　　·第三个域表示域的前缀长度，SQL Server通常会用它来提供最紧凑的文件存储。在创建格式文件时如果你指定-c switch，那么就会自动用到0。

　　·第四个域代表特殊域数据类型字节长度。

　　·第五个域表明如何终止行和列。由于在创建格式文件时用了-t switch，源文件的域值就必须通过逗号终止。

　　·第六个域映射这些列在SQL Server表中的排列顺序。

　　·第七个和最后一个域提供了SQL Server表中字符列的整理信息。

　　要用格式文件将数据导入SalesPeople表中，我们必须如下修改文件：

 　　你可以看到，列的顺序已经进行了修改，这一顺序就是它们在格式文件中的排列顺序。SalesPersonID列现在排在最后，并且以\r\n结尾。LastName列现在排在开头并且以逗号结尾。

　　修改、保存格式文件后，准备在bcp命令中用了。下面的例子说明如何调用格式文件：

 　　注意，你从SalesPeople.txt文件中导入数据时，还必须用到-f switch调用格式文件。还要注意到你现在已经不需要包括-t 和-c switch，因为现在在格式文件中已经包括了这些信息。

　　无论你用的是格式文件还是只运行基本命令，你现在都应该更好地了解到了如何使用bcp工具。记住bcp工具支持的switch比我所列举的要多得多。同时，本技巧还提供了有关启用bcp工具、轻松将数据导入到SQL Server表以及导出数据到文本文件的比较充足的信息。