有关excel表导入sql server 2005 的亲身经历

说明：

一、我装的sql没有导入/导出工具，所以只能用代码来操作；

二、我装的是sqlserver2005开发者版；

三、excel文件名为“行业汇总表.xls”表名为“汇总表1”，存放在桌面上。数据库名为“stone”，表名为“hy_all”。

 

成功经验：

一、在目标表上点右键“编写表脚本为”→“insert”→“新查询编辑器窗口”；

二、删除已有的代码，并将下面的代码帖进去：

 

SET IDENTITY_INSERT [stone].[dbo].[hy_all] ON
INSERT INTO [stone].[dbo].[hy_all](id,hy_code,code,[name],data)
SELECT id,hy_code,code,[name],data
FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=C:/Documents and Settings/Administrator/桌面/行业汇总表.xls',汇总表1$ )

 

解释一下：第一行的目的是把表设置为可写入状态；

　　　　　其他行是插入读取的数据；

　　　　　这里数据库和excel表的字段名是一致的，实际中可以不一致，但必须是一一对应。

 

三、将对应的数据库、表、excel文件及表更新后，点“！执行”即可实现导入数据。

 

可能的错误及解决办法：

一、要将目标excel表关闭，否则无法实现导入操作；

二、id必须是自动增加的，即目的表中必须至少有一个标识字段（我设置的是自动增加１），否则第一行执行打开会出错；

三、尽管id设置了自动增加，仍然需要有数据导入，我的做法是在原excel表中将id用脱放的方式拉出来。

 

 

补充内容：

 

在Sql Server中查询一下Excel文件的时候出现问题：
SELECT *  FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:/a.xls',[sheet1$])
    结果提示：
    SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
查询相关资料，找到解决方法：

    启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
    使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure