一个Excel导入SQL server的例子

时间：2011-05-02 来源：邀月

有人提问如下：

这个是Excel的，比如是test.xls

欠费年份欠费开始月份欠费结束月份应缴金额(月租)

2001 9 12 94.4

2008 5 12 88.8

2010 8 12 90.4

___________________________________________

这个是表：比如是a表

a(pk,int,not null) //主键，自动增长

b(varchar(19),null) //费款所属期

c(decimal(10,2),null) //应缴金额___________________________________________

现在我要将test.xls中的数据导入到a表，从开始月份到结束月份要做循环导入，比如第一条2001年的从9月到12月要录入4条数据到a表，导入后的格式如：

select * from a

a b c

1 2001-09 94.4

2 2001-10 94.4

3 2001-11 94.4

4 2001-12 94.4

数据库是：MS Sql server 2008

解析：

思路一：可以使用OpenRowset查询导入到表变量中，再用游标循环赋值。方法如下：

use testdb2
go
/*******************建立测试数据***[email protected]***********************/
IF NOT OBJECT_ID('[TBTest]') IS NULL
    DROP TABLE [TBTest]
GO
CREATE TABLE [TBTest](
[tid] int identity(1,1) primary key,

[date] NVARCHAR(20) null,
[Money] decimal(10,2) null)
go

/*******************启用Ad Hoc Distributed Queries***[email protected]***********************/

--------USE master
--------go

--------sp_configure 'show advanced options', 1
--------GO
------------reconfigure
----------启用分布式查询　Ad Hoc Distributed Queries
--------sp_configure 'Ad Hoc Distributed Queries', 1
--------GO
--------reconfigure
--------go

use testdb2
go

/*******************定义表变量***[email protected]***********************/

Declare @TableVar table
(PKId int primary key identity(1,1)
,RYear int not null,BMonth int not null
,EMonth int not null,RMoney Decimal(15,2) not null
----,d1 date null,d2 Date null
)

insert into @TableVar
(RYear ,BMonth ,EMonth ,RMoney)
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\test\test20110501.xls',
'select * from [Sheet1$]')
----select RYear,BMonth,EMonth,RMoney from @TableVar
------update @TableVar
------SET d1=cast(
------Cast(RYear as Nvarchar(4))+'-'+cast(BMonth AS nvarchar(2))+'-01' as date)
------, d2=cast(
------Cast(RYear as Nvarchar(4))+'-'+cast(EMonth AS nvarchar(2))+'-01' as date)

/*******************第一种方法，用游标***[email protected]***********************/

    DECLARE @RYear int
    declare @BMonth int
    declare @EMonth int
    declare @RMoney int

    DECLARE DateDemo_cursor CURSOR FOR
    select RYear,BMonth,EMonth,RMoney from @TableVar where 1=1
    OPEN DateDemo_cursor

    FETCH NEXT FROM DateDemo_cursor
    INTO @RYear,@BMonth,@EMonth,@RMoney

        WHILE @@FETCH_STATUS = 0
        BEGIN
        ----print @RYear
        ----print @BMonth
        ----print @EMonth
        ----print @RMoney

            --修改记录
           while(@EMonth-@BMonth>=0)
               begin
                insert INTO [TBTest]
                SELECT TOP 1 cast(RYear  AS nvarchar(4))+'-'+
                CASE WHEN (@BMonth<10) THEN '0'+cast(@BMonth AS nvarchar(2))
                ELSE cast(@BMonth AS nvarchar(2)) END,
                Rmoney from @TableVar where Ryear=@RYear

                SET @BMonth=@BMonth+1
               end
            --修改结束
            FETCH NEXT FROM DateDemo_cursor into @RYear,@BMonth,@EMonth,@RMoney

        END
    CLOSE DateDemo_cursor
    DEALLOCATE DateDemo_cursor

GO
SELECT * FROM [TBTest]

查询结果：

/*
tid    date    Money
1    2001-09    94.40
2    2001-10    94.40
3    2001-11    94.40
4    2001-12    94.40
5    2008-05    88.80
6    2008-06    88.80
7    2008-07    88.80
8    2008-08    88.80
9    2008-09    88.80
10    2008-10    88.80
11    2008-11    88.80
12    2008-12    88.80
13    2010-08    90.40
14    2010-09    90.40
15    2010-10    90.40
16    2010-11    90.40
17    2010-12    90.40
*/

评价：该方法使用了最传统的方法，思路清晰。但没有体现SQL server 2008的语法特性，略显繁琐。

思路二：可否使用CTE实现？

思路三：可否使用SQL Server 2008新提供的Merge实现？

思路四：使用NPOI在业务层实现数据转换。