就下载 —— 安全下载、无毒手机软件、绿色软件官方下载网站最近更新|下载排行|热门标签|收藏本站

您现在的位置是:就下载 > IT资讯 > 软件教程 > 把Excel2003中的数据导入到SQL Server中的几个方法

有人提问如下:

这个是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
/*******************建立测试数据**************************/
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**************************/

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

/*******************定义表变量**************************/

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$]')
 
/*******************第一种方法,用游标**************************/

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实现?(Kill提供)

/*

******************第二种方法,用CTE,适用于sql2005/2008/2008 r2*********/

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表实现(由小F提供)

利用该表,可获取一定区间内的列表,最长不超过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*********/
/**************************************************************/

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实现

上一篇:给力Windows XP添加“管理员取得所有权”右键菜单

本文地址:软件教程 >> http://www.9xz.net/it/ruanjianjiaocheng/19658.html

下一篇:各版本 CorelDRAW 的快捷键

  • 打印
推荐阅读
热门专题
推荐内容
热点内容