在我們的日常工作中,與數據庫打交道的機會越來越多。這一篇文章我整理一下常見的SQL Server導入導出數據的幾個工具
這是一個可視化的工具,我放在首位,是由於它可以極大靈活地滿足導入導出功能,而且是所見即所得的,易於使用。
啟動數據導入導出向導的方式有好多種,我自己習慣直接通過如下的命令啟動(開始=》運行)
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的討論已經超出了本篇文章的范圍。如有興趣,請搜索我其他的文章。
如果你要實現簡單的數據導入導出,並且希望用腳本命令的方式,而不是圖形界面來實現。那麼可以考慮SQL Server提供的BCP實用工具。
上圖演示了如何將一個表導出為Excel文件,但如果想要根據一個查詢導出的話,則可以按照下面這樣的語法
使用BCP也可以進行數據,只要將out改成In即可。
【注意】使用bcp導出數據最大一個問題就是沒有標題行
如果想在T-SQL中直接導入Excel文件的數據,或者TXT文件的數據,則可以了解一下如下兩個特殊的T-SQL語法
BulkInsert的語法大致如下
OpenRowSet的語法大致如下
【備注】關於這兩個語句的詳細用法,請參考SQL Server自帶的聯機叢書。
如果想要導出導入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&aUML;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自帶的聯機叢書
最後介紹一種更加簡單的方法,如果經常需要在Excel中進行數據庫查詢,並且據此做一些進一步的分析。最好的方法是在Excel中直接去導出數據,或者建立查詢
這個做法的好處,是可以在現有Excel中,任何位置放置你需要的數據,而且需要注意的是,這些數據是鏈接到數據庫的,也就是說,如果數據庫的數據發生了更新,則只要刷新一下就可以了.