如何使用Transact-SQL脚本语言导入Excel表

Question

如题所述

Answer 1

一、执行导入脚本前需要做的几项工作
注意：如果以下几步不执行，导入数据时会提示出错，错误信息一般有如下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 FROMOPENROWSET('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;