這個是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 testdb2go/*******************建立測試數據***3w@live.cn***********************/IF NOT OBJECT_ID('[TBTest]') IS NULL    DROP TABLE [TBTest]GOCREATE TABLE [TBTest]([tid] int identity(1,1) primary key,[date] NVARCHAR(20) null,[Money] decimal(10,2) null)go/*******************啟用Ad Hoc Distributed Queries***3w@live.cn***********************/--------USE master--------go--------sp_configure 'show advanced options', 1--------GO------------reconfigure----------啟用分布式查詢　Ad Hoc Distributed Queries--------sp_configure 'Ad Hoc Distributed Queries', 1--------GO--------reconfigure--------gouse testdb2go/*******************定義表變量***3w@live.cn***********************/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$]') /*******************第一種方法，用游標***3w@live.cn***********************/    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_cursorGOSELECT * FROM [TBTest]查詢結果：

/*tid    date    Money1    2001-09    94.402    2001-10    94.403    2001-11    94.404    2001-12    94.405    2008-05    88.806    2008-06    88.807    2008-07    88.808    2008-08    88.809    2008-09    88.8010    2008-10    88.8011    2008-11    88.8012    2008-12    88.8013    2010-08    90.4014    2010-09    90.4015    2010-10    90.4016    2010-11    90.4017    2010-12    90.40*/評價：該方法使用了最傳統的方法，思路清晰。但沒有體現SQL server 2008的語法特性，略顯繁瑣。

思路二：可否使用CTE實現？(KillKill提供)

/*******************第二種方法，用CTE,適用于sql2005/2008/2008 r2*********//***************************************3w@live.cn***********************/ TRUNCATE table [TBTest]goDeclare @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);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$]');with seq as (select top 12 row_number() over (order by object_id) val   from sys.objects)select   cast(t.RYear  AS nvarchar(4))+'-'+        CASE WHEN (t.BMonth+seq.val<10) THEN '0'+cast(t.BMonth+seq.val AS nvarchar(2))        ELSE cast(t.BMonth+seq.val AS nvarchar(2)) END        ,RMoney cfrom @TableVar t inner join seq on t.BMonth+seq.val <= EMonth;思路三：可否使用SQL Server 2008新提供的Merge實現？

思路四：使用NPOI在業務層實現數據轉換。

思路五：用Master..spt_values表實現(由小Ｆ提供）

利用該表，可獲取一定區間內的列表,最長不超過2048，如

select number from master..spt_valueswhere type='P' andnumber between 1 and 5/*number12345*/因為月份最多12，不超過2048,因此可以利用 master..spt_values。

/*******************第五種方法，用master..spt_values,適用于sql2005/2008/2008 r2*********//***************************************3w@live.cn***********************/ 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   tid=row_number()over(order by getdate()),ltrim(RYear)+'-'+ltrim(right(100+number,2)) as date,　b.RMoney as moneyfrom   master..spt_values a, @TableVar bwhere   number between BMonth and EMonthand   type='p'

思路六：使用SSIS實現

關鍵字：Excel、導入、SQL Server、數據