[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
--------go

use testdb2
go

/*******************定义表变量***3w@live.cn***********************/

Declare@TableVartable
(PKId intprimarykeyidentity(1,1)
,RYear intnotnull,BMonth intnotnull
,EMonth intnotnull,RMoney Decimal(15,2) notnull
----,d1 date null,d2 Date null
)

insertinto@TableVar
(RYear ,BMonth ,EMonth ,RMoney)
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\test\test20110501.xls',
'select * from [Sheet1$]')
 
/*******************第一种方法，用游标***3w@live.cn***********************/

DECLARE@RYearint
declare@BMonthint
declare@EMonthint
declare@RMoneyint

DECLARE DateDemo_cursor CURSORFOR
select RYear,BMonth,EMonth,RMoney from@TableVarwhere1=1
OPEN DateDemo_cursor

FETCHNEXTFROM 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
insertINTO[TBTest]
SELECTTOP1cast(RYear ASnvarchar(4))+'-'+
CASEWHEN (@BMonth<10) THEN'0'+cast(@BMonthASnvarchar(2))
ELSEcast(@BMonthASnvarchar(2)) END,
Rmoney from@TableVarwhere Ryear=@RYear

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

END
CLOSE DateDemo_cursor
DEALLOCATE DateDemo_cursor

GO
SELECT*FROM[TBTest]

TRUNCATEtable[TBTest]
go

Declare@TableVartable
(PKId intprimarykeyidentity(1,1)
,RYear intnotnull,BMonth intnotnull
,EMonth intnotnull,RMoney Decimal(15,2) notnull
);

insertinto@TableVar(RYear ,BMonth ,EMonth ,RMoney)
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\test\test20110501.xls',
'select * from [Sheet1$]');

with seq as (selecttop12 row_number() over (orderbyobject_id) val
from sys.objects)
select
cast(t.RYear ASnvarchar(4))+'-'+
CASEWHEN (t.BMonth+seq.val<10) THEN'0'+cast(t.BMonth+seq.val ASnvarchar(2))
ELSEcast(t.BMonth+seq.val ASnvarchar(2)) END
,RMoney c
from@TableVar t innerjoin seq
on t.BMonth+seq.val <=

Declare@TableVartable
(PKId intprimarykeyidentity(1,1)
,RYear intnotnull,BMonth intnotnull
,EMonth intnotnull,RMoney Decimal(15,2) notnull
----,d1 date null,d2 Date null
);

insertinto@TableVar
(RYear ,BMonth ,EMonth ,RMoney)
select*fromOpenRowSet('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(orderbygetdate()),ltrim(RYear)+'-'+ltrim(right(100+number,2)) as date,
b.RMoney asmoney
from
master..spt_values a, @TableVar b
where
numberbetween BMonth and EMonth
and
type='p'