常見SQL Server匯入匯出資料的幾個工具

標籤：blog   http   使用   os   io   檔案   資料   for   

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

 

在我們的日常工作中，與資料庫打交道的機會越來越多。這一篇文章我整理一下常見的SQL Server匯入匯出資料的幾個工具

 

1. 資料匯入匯出嚮導

這是一個可視化的工具，我放在首位，是由於它可以極大靈活地滿足匯入匯出功能，而且是所見即所得 (WYSIWYG)的，便於使用。

啟動資料匯入匯出嚮導的方式有好多種，我自己習慣直接通過如下的命令啟動(開始=》運行）

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 NorthwindGOSELECT * FROM Orders     FOR XML RAW(‘OrderItem‘),    ELEMENTS XSINIL,    ROOT(‘Orders‘) 

上面的文法，可以將查詢用XML格式返回，如所示

【備忘】FOR XML是SELECT的一個子句，有關更多用法，請參考SQL Server內建的聯機叢書

【備忘】匯出為XML格式的目的是為了更好地在不同應用程式之間共用。

 

反過來，如果我們得到了一段XML資料，想將其匯入到SQL Server中某個表中。可以考慮用OPENXML的文法。它的作用就是將XML還原為行集資料，然後就可以插入到我們的目的表中去了。

DECLARE @x XMLDECLARE @docHandle intSET @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--第二步，openxmlINSERT Orders SELECT * FROM OPENXML(@docHandle,N‘/Orders/OrderItem‘,2) WITH Orders--第三步，銷毀EXEC sp_xml_removedocument @docHandle  

【備忘】OPENXML還有其他更加複雜的用法，請參考SQL Server內建的聯機叢書

 

5.使用Excel匯出資料，或者建立查詢

最後介紹一種更加簡單的方法，如果經常需要在Excel中進行資料庫查詢，並且據此做一些進一步的分析。最好的方法是在Excel中直接去匯出資料，或者建立查詢

這個做法的好處，是可以在現有Excel中，任何位置放置你需要的資料，而且需要注意的是，這些資料是連結到資料庫的，也就是說，如果資料庫的資料發生了更新，則只要重新整理一下就可以了。