一个Excel导入SQL Server的例子

编者注：你可曾想过要如何将Excel表中的数据导入到SQL Server中？在导入的时候，你是否能找到自己的主键呢？笔者通过一个例子告诉你，该怎么导入，希望对大家有所帮助。

有人提问如下：

这个是Excel的，比如是test.xls

欠费年份欠费开始月份欠费结束月份应缴金额(月租)

2001 9 1294.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 testdb2go/*******************建立测试数据***[email protected]***********************/IFNOTOBJECT_ID('[TBTest]') ISNULLDROPTABLE[TBTest]GOCREATETABLE[TBTest]([tid]intidentity(1,1) primarykey,[date]NVARCHAR(20) null,[Money]decimal(10,2) null)go/*******************启用Ad Hoc Distributed Queries***[email protected]***********************/--------USE master--------go--------sp_configure 'show advanced options', 1--------GO------------reconfigure----------启用分布式查询　Ad Hoc Distributed Queries--------sp_configure 'Ad Hoc Distributed Queries', 1--------GO--------reconfigure--------gouse testdb2go/*******************定义表变量***[email protected]***********************/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$]')/*******************第一种方法，用游标***[email protected]***********************/DECLARE@RYearintdeclare@BMonthintdeclare@EMonthintdeclare@RMoneyintDECLARE DateDemo_cursor CURSORFORselect RYear,BMonth,EMonth,RMoney from@TableVarwhere1=1OPEN DateDemo_cursorFETCHNEXTFROM DateDemo_cursorINTO@RYear,@BMonth,@EMonth,@RMoneyWHILE@@FETCH_STATUS=0BEGIN----print @RYear----print @BMonth----print @EMonth----print @RMoney--修改记录while(@EMonth-@BMonth>=0)begininsertINTO[TBTest]SELECTTOP1cast(RYear ASnvarchar(4))+'-'+CASEWHEN (@BMonth<10) THEN'0'+cast(@BMonthASnvarchar(2))ELSEcast(@BMonthASnvarchar(2)) END, Rmoney from@TableVarwhere Ryear=@RYearSET@BMonth=@BMonth+1end--修改结束FETCHNEXTFROM DateDemo_cursor into@RYear,@BMonth,@EMonth,@RMoneyENDCLOSE DateDemo_cursorDEALLOCATE DateDemo_cursorGOSELECT*FROM[TBTest]

查询结果：

/*tid date Money1 2001-09 94.402 2001-10 94.403 2001-11 94.404 2001-12 94.405 2008-05 88.806 2008-06 88.807 2008-07 88.808 2008-08 88.809 2008-09 88.8010 2008-10 88.8011 2008-11 88.8012 2008-12 88.8013 2010-08 90.4014 2010-09 90.4015 2010-10 90.4016 2010-11 90.4017 2010-12 90.40*/

评价：该方法使用了最传统的方法，思路清晰。但没有体现SQL server 2008的语法特性，略显繁琐。

思路二：可否使用CTE实现？(KillKill提供)

/*

******************第二种方法，用CTE,适用于sql2005/2008/2008 r2*********//***************************************[email protected]***********************/TRUNCATEtable[TBTest]goDeclare@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)selectcast(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 cfrom@TableVar t innerjoin seq on t.BMonth+seq.val <=

 EMonth;

思路三：可否使用SQL Server 2008新提供的Merge实现？

思路四：使用NPOI在业务层实现数据转换。

思路五：用Master..spt_values表实现(由小Ｆ提供）

利用该表，可获取一定区间内的列表,最长不超过2048，如

selectnumberfrom master..spt_valueswhere type='P'andnumberbetween1and5/*number12345*/

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

/*******************第五种方法，用master..spt_values,适用于sql2005/2008/2008 r2*********//***************************************[email protected]***********************/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 asmoneyfrom master..spt_values a, @TableVar bwherenumberbetween BMonth and EMonthand type='p'

思路六：使用SSIS实现

原文链接：http://www.cnblogs.com/downmoon/archive/2011/05/02/2034191.html

【编辑推荐】

晒晒我的通用数据访问层

几步走，教你创建简单访问数据库方法

一步一步设计你的数据库1