sql 语句导入 excel 实验

再讲今天的主题之前，先说一些废话，看下面。

最近要做了一个县的村级网站，本县有40个乡镇，每个乡有10-20个不等网站，一共有800多个村，网站经过一段时间已经做出来了，本人用.net开发的，类似自助建站。

但现在面临一个难题：数据库的录入

目前的情况是：导入以前的村级信息，以及大量等待录入的信息。

这么多的数据如果一条条录入，工作量是非常大的。

最后我决定批量录入。

对于以前的存在的数据，只需复制表过来就行，但结构有点不同。

下面介绍下我的解决方式：

1：首先插入 所以村级的名字

使用一个函数，批量插入

1 首先自定义sql 函数

CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+\'a\')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,\'\')
END
INSERT @re VALUES(@s)
RETURN
END

2 插入表中
insert into 村级(村名)select * from dbo.f_splitSTR(\'村名1,‘村名2’,\'村名3\'

2：对于以前的数据库，以前是每个村一个数据库（现在我设计的一个乡一个数据库库）首先使用sql语句复制以前的数据

由于以前的表的结构与现在的表结构不同。

sql语句如下：                                                                                                                              

insert into v_news(title,content,posttime,Author,picpath,villageID,classID)
select 标题,内容,发布时间,作者,图片地址,7,case 信息类别 
when \'*评议\'  then 6 when \'惠民政策\' then 4 
when \'个性事务\' then 7  when \'财务收支\' then 5 end  from elg.dbo.新闻

3：（今天的主题）对于未录入的资料，我打算采用sql语句导入excel

下面我来做一些实验：

  1）：首先创建实验数据库和实验表

语句如下：

create database excel_test

 use excel_test
 create table test
(
 id int identity(1,1) primary key,
 name1 varchar(200), 
 name2 ntext,
 name3 ntext
)

2）创建excel表，命名为test.xls,结构图片如下：

 3）现在我们来导入这个excel表里面的数据，执行下面的sql语句：

insert into  test(name1,name2,name3) 
 select * from 
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\Desktop\test.xls\', [sheet1$])

（这里注意下因为表中的id为自动增长，所有不能这样写：insert into test select * from...）

结构显示为：

（所影响的行数为 1 行）

按理来说应该是2行。

我们先 用sql查询语句 select * from test

显示的结果为

怎么只有一条数据呢？我想了下可能是两条数据一样，难道他还自动过滤了？也没去多想了，

接下来我在想能不能插入test.xsl指定的列呢？

我执行如下语句：

insert into  test(name1,name2,name3) 
 select * from 
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\Desktop\test.xls\', \'select A,B,C from [sheet1$]\') --A，B，C是我误认为的test.xsl的列

结果显示为：

服务器: 消息 7354，级别 16，状态 1，行 1

OLE DB 提供程序 \'MICROSOFT.JET.OLEDB.4.0\' 为列 \'A\' 提供的元数据无效。The data type is not supported.

看来A，B,C不是excel的列，要么是 第一列的aa,bb,cc?为了区别列和内容我在test.xsl的表插入一行数据

如下图：

这个时候我再执行：

insert into  test(name1,name2,name3) 

 select * from 
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\Desktop\test.xls\', \'select 1,2,3 from [sheet1$]\'

这个时候的结果为：

服务器: 消息 206，级别 16，状态 2，行 1

操作数类型冲突: int 与 ntext 不兼容

服务器: 消息 206，级别 16，状态 1，行 1

操作数类型冲突: int 与 ntext 不兼容

似乎有点柳暗花明了。这个我想到这个excel表里面究竟是什么数据，为什么会出现不兼容执行了如下语句：

 select * from 
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\Desktop\test.xls\', \'select 1,2,3 from [sheet1$]\'

结果显示为：

这么说 1，2，3也不是excel表的列，几乎要否定了第一列就是 excel表的列名了。在这个时候我执行了下面语句

 select * from 
OPENROWSET(\'MICROSOFT.JET.OLEDB.4.0\'
,\'Excel 5.0;HDR=YES;DATABASE=C:\Documents and Settings\Administrator\Desktop\test.xls\', [sheet1$])

结果为：

晕死 excel表的第一列1,2,3去哪了？列名怎么又是F1,F2，F3？莫非他的列都是F1,F2等依次类推？又或者是列名就是第一列加个F？

带着这个疑问 我又在excel表新增了一列字母列。

如下图：

这个时候我再执行上次相容的sql语句：

结果如下：

这个时候就清晰了，excel表第一列就相当于sql中的列名，不过我们也发现一个问题最后查询的sql第一列都为null，估计在excel中要转换为字符吧。不怎么熟悉，呵呵。如果为数字的话 查询列名要加F，呵呵 ，写了一个下午，中间开会N场。

欢迎各位指点。