常见SQL Server导入导出数据的几个工具

2022-09-20 15:01:13

摘自：http://www.cnblogs.com/chenxizhang/archive/2011/06/09/2076542.html

在我们的日常工作中，与数据库打交道的机会越来越多。这一篇文章我整理一下常见的SQL Server导入导出数据的几个工具

1. 数据导入导出向导

这是一个可视化的工具，我放在首位，是由于它可以极大灵活地满足导入导出功能，而且是所见即所得的，易于使用。

启动数据导入导出向导的方式有好多种，我自己习惯直接通过如下的命令启动(开始=》运行）

dtswizard（顾名思义，它是一个wizard——向导，而且是与dts——data transfomation service有关的)

从下图可以看出，这个工具支持多种不同类型的数据源（以及数据目标），它其实不仅仅限于SQL Server服务器。

【注意】如果是64位，这里的提供程序中找不到Excel和Access（我知道很多朋友都想导出这两种格式）

该向导还可以通过在SQL Server Management Studio(SSMS)中启动。如果数据源或者数据目标是SQL Server的话，这是更加方便一些的。

【注意】通过这样的方式启动的向导，却又可以看到Excel和Access（很神奇吧，）

值得一提的是，这个导入导出向导还有一个好处，就是将我们经常需要导入导出的操作保存起来，如下图所示

这里有一个所谓的SSIS Package，是什么意思呢？SSIS指的是SQL Server Integration Service，它是微软SQL Server BI平台的一个重要组件，用来设计和管理ETL解决方案。

这个SSIS Package是一个扩展名为dtsx的特殊文件包，它可以通过一个所谓的Business Intelligence Developement Studio(BI Studio）打开查看，并且还可以进一步地编辑

【备注】SSIS的讨论已经超出了本篇文章的范围。如有兴趣，请搜索我其他的文章。

2.BCP

如果你要实现简单的数据导入导出，并且希望用脚本命令的方式，而不是图形界面来实现。那么可以考虑SQL Server提供的BCP实用工具。

上图演示了如何将一个表导出为Excel文件，但如果想要根据一个查询导出的话，则可以按照下面这样的语法

使用BCP也可以进行数据，只要将out改成In即可。

【注意】使用bcp导出数据最大一个问题就是没有标题行

3. Bulk Insert和OpenRowSet

如果想在T-SQL中直接导入Excel文件的数据，或者TXT文件的数据，则可以了解一下如下两个特殊的T-SQL语法

BulkInsert的语法大致如下

OpenRowSet的语法大致如下

【备注】关于这两个语句的详细用法，请参考SQL Server自带的联机丛书。

4.FORXML和OPENXML

如果想要导出导入XML格式的数据，则可以了解一下FORXML和OPENXML语法（它们是T-SQL语法，所以也可以很灵活地嵌入在我们的存储过程中）

USE Northwind
GO

SELECT * FROM Orders 
    FOR XML RAW(‘OrderItem‘),
    ELEMENTS XSINIL,
    ROOT(‘Orders‘)

上面的语法，可以将查询用XML格式返回，如下图所示

【备注】FOR XML是SELECT的一个子句，有关更多用法，请参考SQL Server自带的联机丛书

【备注】导出为XML格式的目的是为了更好地在不同应用程序之间共享。

反过来，如果我们得到了一段XML数据，想将其导入到SQL Server中某个表中。可以考虑用OPENXML的语法。它的作用就是将XML还原为行集数据，然后就可以插入到我们的目的表中去了。

DECLARE @x XML
DECLARE @docHandle int

SET @x=N‘<Orders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OrderItem>
    <OrderID>10248</OrderID>
    <CustomerID>VINET</CustomerID>
    <EmployeeID>3</EmployeeID>
    <OrderDate>1996-07-04T00:00:00</OrderDate>
    <RequiredDate>1996-08-01T00:00:00</RequiredDate>
    <ShippedDate>1996-07-16T00:00:00</ShippedDate>
    <ShipVia>3</ShipVia>
    <Freight>32.3800</Freight>
    <ShipName>Vins et alcools Chevalier</ShipName>
    <ShipAddress>59 rue de lAbbaye</ShipAddress>
    <ShipCity>Reims</ShipCity>
    <ShipRegion xsi:nil="true" />
    <ShipPostalCode>51100</ShipPostalCode>
    <ShipCountry>France</ShipCountry>
  </OrderItem>
  <OrderItem>
    <OrderID>10249</OrderID>
    <CustomerID>TOMSP</CustomerID>
    <EmployeeID>6</EmployeeID>
    <OrderDate>1996-07-05T00:00:00</OrderDate>
    <RequiredDate>1996-08-16T00:00:00</RequiredDate>
    <ShippedDate>1996-07-10T00:00:00</ShippedDate>
    <ShipVia>1</ShipVia>
    <Freight>11.6100</Freight>
    <ShipName>Toms Spezialitäten</ShipName>
    <ShipAddress>Luisenstr. 48</ShipAddress>
    <ShipCity>Münster</ShipCity>
    <ShipRegion xsi:nil="true" />
    <ShipPostalCode>44087</ShipPostalCode>
    <ShipCountry>Germany</ShipCountry>
  </OrderItem></Orders>‘
  
  

--第一步，做准备
EXEC SP_XML_PREPAREDOCUMENT @docHandle OUTPUT,@x
--第二步，openxml
INSERT Orders SELECT * FROM OPENXML(@docHandle,N‘/Orders/OrderItem‘,2) WITH Orders
--第三步，销毁
EXEC sp_xml_removedocument @docHandle

【备注】OPENXML还有其他更加复杂的用法，请参考SQL Server自带的联机丛书