一個(gè)Excel導(dǎo)入SQL Server的例子
編者注：你可曾想過要如何將Excel表中的數(shù)據(jù)導(dǎo)入到SQL Server中？在導(dǎo)入的時(shí)候，你是否能找到自己的主鍵呢？筆者通過一個(gè)例子告訴你，該怎么導(dǎo)入，希望對(duì)大家有所幫助。
有人提問如下：
這個(gè)是Excel的，比如是test.xls
欠費(fèi)年份 欠費(fèi)開始月份 欠費(fèi)結(jié)束月份 應(yīng)繳金額(月租)
2001 9 12 94.4
2008 5 12 88.8
2010 8 12 90.4
___________________________________________
這個(gè)是表：比如是a表
a(pk,int,not null) //主鍵，自動(dòng)增長(zhǎng)
b(varchar(19),null) //費(fèi)款所屬期
c(decimal(10,2),null) //應(yīng)繳金額
___________________________________________
現(xiàn)在我要將test.xls中的數(shù)據(jù)導(dǎo)入到a表，從開始月份到結(jié)束月份要做循環(huán)導(dǎo)入，比如第一條2001年的從9月到12月要錄入4條數(shù)據(jù)到a表，導(dǎo)入后的格式如：
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
數(shù)據(jù)庫(kù)是：MS Sql server 2008
解析：
思路一：可以使用OpenRowset查詢導(dǎo)入到表變量中，再用游標(biāo)循環(huán)賦值。方法如下：
use testdb2
go
/*******************建立測(cè)試數(shù)據(jù)***3w@live.cn***********************/
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***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 @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$]')
/*******************第一種方法，用游標(biāo)***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
--修改結(jié)束
FETCH NEXT FROM DateDemo_cursor into @RYear,@BMonth,@EMonth,@RMoney
END
CLOSE DateDemo_cursor
DEALLOCATE DateDemo_cursor
GO
SELECT * FROM [TBTest]
查詢結(jié)果：
/*
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
*/
評(píng)價(jià)：該方法使用了最傳統(tǒng)的方法，思路清晰。但沒有體現(xiàn)SQL server 2008的語(yǔ)法特性，略顯繁瑣。
思路二：可否使用CTE實(shí)現(xiàn)？(KillKill提供)
/*
******************第二種方法，用CTE,適用于sql2005/2008/2008 r2*********/
/***************************************3w@live.cn***********************/
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實(shí)現(xiàn)？
思路四：使用NPOI在業(yè)務(wù)層實(shí)現(xiàn)數(shù)據(jù)轉(zhuǎn)換。
思路五：用Master..spt_values表實(shí)現(xiàn)(由?。铺峁?/strong>
利用該表，可獲取一定區(qū)間內(nèi)的列表,最長(zhǎng)不超過2048，如
select number from master..spt_values
where type='P' and
number between 1 and 5
/*
number
1
2
3
4
5
*/
因?yàn)樵路葑疃?2，不超過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 money
from
master..spt_values a, @TableVar b
where
number between BMonth and EMonth
and
type='p'
思路六：使用SSIS實(shí)現(xiàn)
原文鏈接：http://www.cnblogs.com/downmoon/archive/2011/05/02/2034191.html
【編輯推薦】
- 曬曬我的通用數(shù)據(jù)訪問層
- 幾步走，教你創(chuàng)建簡(jiǎn)單訪問數(shù)據(jù)庫(kù)方法
- 一句代碼實(shí)現(xiàn)批量數(shù)據(jù)綁定 下
- 一步一步設(shè)計(jì)你的數(shù)據(jù)庫(kù)1
- 不重復(fù)隨機(jī)數(shù)列生成算法
