建站
咨詢
售后
建站咨詢
新聞中心
編者注：你可曾想過要如何將Excel表中的數(shù)據(jù)導(dǎo)入到SQL Server中？在導(dǎo)入的時候，你是否能找到自己的主鍵呢？筆者通過一個例子告訴你，該怎么導(dǎo)入，希望對大家有所幫助。
有人提問如下：
這個是Excel的，比如是test.xls
欠費年份 欠費開始月份 欠費結(jié)束月份 應(yīng)繳金額(月租)
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) //應(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ù)庫是：MS Sql server 2008
解析：
思路一：可以使用OpenRowset查詢導(dǎo)入到表變量中，再用游標(biāo)循環(huán)賦值。方法如下：
use
testdb2
go
/*
******************建立測試數(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
*/
評價：該方法使用了最傳統(tǒng)的方法，思路清晰。但沒有體現(xiàn)SQL server 2008的語法特性，略顯繁瑣。
思路二：可否使用CTE實現(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實現(xiàn)？
思路四：使用NPOI在業(yè)務(wù)層實現(xiàn)數(shù)據(jù)轉(zhuǎn)換。
思路五：用Master..spt_values表實現(xiàn)(由?。铺峁?/strong>
利用該表，可獲取一定區(qū)間內(nèi)的列表,最長不超過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********
*/
/*
**************************************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實現(xiàn)
網(wǎng)頁名稱：一個Excel導(dǎo)入SQL Server的例子
轉(zhuǎn)載注明：http://m.5511xx.com/article/coecgij.html
