一个Excel导入SQL server的例子（分别使用游标、CTE、master..spt_values实现）

SQL Server 2008中SQL应用系列--目录索引

有人提问如下：

这个是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
/*******************建立测试数据***[e-mail]***********************/
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***[e-mail]***********************/ 
--------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
/*******************定义表变量***[e-mail]***********************/ 
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
)
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)
/*******************第一种方法，用游标***[e-mail]***********************/ 
	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
        
            --修改记录
           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实现？(KillKill提供)

/*******************第二种方法，用CTE,适用于sql2005/2008/2008 r2*********/
/***************************************[e-mail]***********************/ 
TRUNCATE table [TBTest]
go
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
);
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 c
from @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_values
where type='P' and
number between 1 and 5
/*
number
1
2
3
4
5
*/ 

因为月份最多12，不超过2048,因此可以利用 master..spt_values。

/*******************第五种方法，用master..spt_values,适用于sql2005/2008/2008 r2*********/
/***************************************[e-mail]***********************/
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 money
from
master..spt_values a, @TableVar b
where
number between BMonth and EMonth
and
type='p'

思路六：使用SSIS实现

SSIS导入IIS 日志的简单示例

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! [e-mail]