SQL-从Excel文件导入信息到数据库表

1.要实现的效果

2.SQL脚本

/*
    时间：2018-6-22
    人员：LDH
    功能：SQL-从Excel文件导入信息到数据库表
*/


USE TestDB;
GO

IF OBJECT_ID('StudentsInfo', 'U') IS NOT NULL
    DROP TABLE dbo.StudentsInfo;

CREATE TABLE StudentsInfo 
(
    No char(10) NOT NULL PRIMARY KEY,--学号是主键
    Name nvarchar(20) NULL,--姓名
    Sex char(2),--性别
    Age int NULL --年龄
)

INSERT INTO StudentsInfo
(   No,
    Name,
    Sex,
    Age
)
VALUES
('001', N'zhangsan', '女', 26),
('002', N'lisi', '男', 26);

--    EXEC sp_addextendedproperty 'MS_Description', '在此填写你的栏为注释', 'user', 此处为你的表名称, 'table', 此处为你的表名, 'column', 在此写你的字段名称
-- 给表中栏位添加注释
EXEC sp_addextendedproperty 'MS_Description', '学号', 'user', dbo, 'table', StudentsInfo, 'column', No;
EXEC sp_addextendedproperty 'MS_Description', '姓名', 'user', dbo, 'table', StudentsInfo, 'column', Name;
EXEC sp_addextendedproperty 'MS_Description', '性别', 'user', dbo, 'table', StudentsInfo, 'column', Sex;
EXEC sp_addextendedproperty 'MS_Description', '年龄', 'user', dbo, 'table', StudentsInfo, 'column', Age;

-- 查询表中数据
SELECT No,
    Name,
    Sex,
    Age
FROM dbo.StudentsInfo;

-- 清空表中数据
DELETE FROM dbo.StudentsInfo;


/*
------------------------------------------------------------------------------------------------------------------------
SQL从Excel文件导入信息到数据库表 
Jet 引擎，可以访问 Office 97-2003，但不能访问 Office 2007
Office2003-'Microsoft.Jet.OLEDB.4.0'
ACE 引擎是随 Office 2007 一起发布的数据库连接组件，既可以访问 Office 2007，也可以访问 Office 97-2003
Office2007-'Microsoft.ACE.OLEDB.4.0'
Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的
Office2016-'Microsoft.ACE.OLEDB.12.0'
------------------------------------------------------------------------------------------------------------------------
*/


/*
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，
因为此组件已作为此服务器安全配置的一部分而被关闭。
系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。开启方法如下:
*/

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO

RECONFIGURE;
GO

-----------------------------------------------------------------------------------------------------------------------
-- 无法创建链接服务器“(null)”的 OLE DB 访问接口“MSDASC”的实例
-- 1.将SQL Server（MSSQLSERVER）和SQL Full-Filter Deamon Launcher（MSSQLSERVER）两个服务的登录身份改为LocalSystem 
-- 2. Management Studio--->服务器对象-->链接服务器-->Microsoft.Jet.OLEDB.4.0-->右键属性-->访问接口选项：全都不选
-----------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
-- 无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Ace.OLEDB.12.0" 的实例

-- 开启导入功能
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- 允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
    N'AllowInProcess',
    1;

-- 允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0',
    N'DynamicParameters',
    1;
--------------------------------------------------------------------------------------------------

--【1】在本地做好要导入数据的Excel文件
--【2】将Excel文件里内容插入到临时表中

SELECT
    * INTO #tempT01
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source="G:/ExportName.xlsx";User ID=Admin;Password=;Extended properties=Excel 5.0')...[sheet1$]
-- 要从指定Excel文件导入数据库的Excel文件的第一个Sheet名字必须为sheet1

--【3】查询导入的临时表中数据

SELECT *
FROM #tempT01;


INSERT INTO dbo.StudentsInfo
(   No,
    Name,
    Sex,
    Age
)
SELECT *
FROM #tempT01;

--【4】查询插入Excel文件里数据到数据库之后，进行查询
SELECT No,
    Name,
    Sex,
    Age
FROM dbo.StudentsInfo;

--【5】删除临时表
DROP TABLE #tempT01

3.执行结果