在我们的日常工作中,与数据库打交道的机会越来越多。这一篇文章我整理一下常见的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还原为行集数据,然后就可以插入到我们的目的表中去了。
第一步,做准备 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自带的联机丛书
5.使用Excel导出数据,或者建立查询
最后介绍一种更加简单的方法,如果经常需要在Excel中进行数据库查询,并且据此做一些进一步的分析。最好的方法是在Excel中直接去导出数据,或者建立查询
这个做法的好处,是可以在现有Excel中,任何位置放置你需要的数据,而且需要注意的是,这些数据是链接到数据库的,也就是说,如果数据库的数据发生了更新,则只要刷新一下就可以了。