【SQL Server技巧篇】如何使用Transact-SQL脚本语言导入Excel表

由于公司不向普通员工开放自有系统后台数据库的接入，但有时需要处理庞大数据量或输出特殊分析报表，这时候如果用Excel就比较卡顿了，处理时间长，报表也不一定能一步到位做出来。遇到这种情况，小屌我喜欢把Excel表导入数据库处理。SQL Server已经提供了良好的数据导入界面，但步骤较多，操作不够方便，个人不太喜欢。后来听说可以用脚本导入Excel表，欣喜之余做了尝试，期间出现各种问题，搜了很多帖子，最终完美实现预期。由于找不到一篇完整描述全过程的帖子，且部分帖子有错误，中间走了较多弯路。故写下本帖，记录全过程以及期间遇到的问题，方便记性不好的自己随时翻看，同时也希望对后来人有所帮助。

 

--------------------本帖环境：SQL Server 2008(32位)+Win7(32位)----------------------

 

一、执行导入脚本前需要做的几项工作

注意：如果以下几步不执行，导入数据时会提示出错，错误信息一般有如下3种（可能不限于此）：

1、开启Ad HocDistributed Queries服务：

开启方式如下：

--开启AdHoc Distributed Queries:

sp_configure 'show advanced options',1;

RECONFIGURE;

sp_configure 'Ad Hoc Distributed Queries',1;

RECONFIGURE;

2、Microsoft.Jet.OLEDB.4.0引擎设置阻止了导入和导出：

设置方式如下（以SQL Server 2008为例)：

SQL Server Management Studio à服务器对象à链接服务器à访问接口àMicrosoft.Jet.OLEDB.4.0à右键属性à访问接口选项：全都不选

3、SQL Server（MSSQLSERVER）和SQL Full-FilterDeamon Launcher 服务的登录身份设置错误：

设置方式如下：

打开SQLServer Configuration Management，将SQL Server（MSSQLSERVER）和SQL Full-text Filter Daemon Launcher（MSSQLSERVER）两个服务的登录身份改为LocalSystem。

4、确保要导入的数据格式正确

5、确保在导入数据时，Excel表或者其他数据库表没有被其他软件打开

6、确定你的Office版本，选择合适的SQL语句进行导入（具体语句后面会提到）

 

二、导入数据并新增表

1、Excel表为xls格式

SELECT * INTO NewXls FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=G:\示例\XLS格式.xls',sheet1$)

2、Excel表为xlsx格式

SELECT * INTO NewXlsx FROM

OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','DataSource=G:\示例\XLSX格式.xlsx;Extended Properties=Excel 12.0')...[sheet1$]

 

三、导入完成后，关闭Ad HocDistributed Queries

       导入数据完成后，记得要关闭Ad Hoc Distributed Queries，因为这是一个安全隐患。关闭方法如下：

--使用完成后,关闭AdHoc Distributed Queries:

sp_configure 'Ad Hoc Distributed Queries',0;

RECONFIGURE;

sp_configure 'show advanced options',0;

RECONFIGURE;