有人提问如下:
这个是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
--启用Ad Hoc Distributed Queries:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--使用完成后,关闭Ad Hoc Distributed Queries:
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
解析:
思路一:可以使用OpenRowset查询导入到表变量中,再用游标循环赋值。方法如下:
use testdb2
go
/*******************建立测试数据***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
)
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 RYear,BMonth,EMonth,RMoney from @TableVar
------update @TableVar
------SET d1=cast(
------Cast(RYear as Nvarchar(4))+'-'+cast(BMonth AS nvarchar(2))+'-01' as date)
------, d2=cast(
------Cast(RYear as Nvarchar(4))+'-'+cast(EMonth AS nvarchar(2))+'-01' as date)
/*******************第一种方法,用游标***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
--修改记录
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实现?(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实现?
思路四:使用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*********/
/***************************************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'
分享到:
相关推荐
将Excel中的数据导入到SqlServer中的实现方法总结。还有一个简单的例子
一个将excel文件导入到SQLServer表中的例子1、连接到sqlserver20002、打开一个excel文件3、选择数据库的表名(回车)4、选择要导入的列(对应的字段名)5、导入到表中
将Excel数据导入到SQLServer中,可以选择要导入的Excel文件和Sheet名称,表名。通过自动编写存储过程来实现导入功能。实际使用无须这么麻烦,可以作为数据导入和存储过程参考例子。
在网上看到许多关于excel文件导入到sqlserver数据库中的例子,不是很全还有许多有错误的,本人经过搜集现分享自己写的代码,注意:此excel文件为有文本格式的,不是循环每行导入的!
使用环境:Win10 x64 Python:3.6.4 SqlServer:2008R2 因为近期需要将excel导入到SQL Server,但是使用的是其他语言,闲来无事就尝试着用python进行导入,速度还是挺快的,1w多条数据,也只用了1s多,代码也比较...
上传+导入sql的代码(EXCEL到入到sqlserver数据库案例)
本例子为 .Net使用NOPI从数据库导出数据到Excel的Demo。有详细的文档和例子说明。也可以从Excel导入数据到SQL Server。
[SQL]将Excel表数据导入SQL Server2005的几种方法归纳 数据库 2010-07-27 11:14:26 阅读201 评论0 字号:大中小 订阅 近日在巨轮着手车间负荷数据处理,反馈回来的数据是保存在Excel文件中的,我必须将其导入SQL ...
delphi Excel 导入的例子 绝对经典. 不需要依赖EXCEL. 不需要安装,只需要引用Sources
ASP中上传EXCEL_再把EXCEL导入到SQL中的例子_带上传功能,本人测试过
从SQL Server中导入/导出 Excel 的基本方法及sql例子
介绍就不多说了,下边是部分目录,觉得有用的话就顶一个 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整...
sqlserver例子: 登陆11.8.126.181数据库的test用户,裝载f: est.xlsx的excel数据到tmp_i表 java -jar -db sqlserver -i 11.8.126.181 -s abcd -u test -w test -f f: est.xlsx -t tmp_i -ff xls mysql例子: 登陆本机...
卷序列号码为 00000030 4489:1826 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql ...│ 将某个目录上的Excel表,导入到数据库中.sql │ 数据导入导出基本方法.sql │ 用ASP上传&下载文件.sql
C#操作sqlce数据导入SQLServer数据库 以及导出成Excel文件示例代码
这意味着对于每个 SQL Server 实例,都存在一个专用的 MSFTESQL 实例,其中包括专用的组件(例如断字符和筛选器)、资源(例如内存)和配置(例如服务级设置,实例级的 resource_usage 是一个更具体的例子)。...
数据字典随库工具 测试数据库:sqlserver 2005.... (1)程序中有数据字典的一个样板文件例子:在“增加表及字段1.1.xls”中。 (2)先建立数据库,在程序的数据库输入处,修改相应的数据库连接方式,然后点操作按钮即可.
对于将表格数据导入到EXcel的方法,NPOI提供了强大的支持,里面含例子包括winform 和web,然后还有数据库通用操作类
55 实例052 为TextBox控件添加列表选择框 57 2.2 Button控件应用 58 实例053 在Button按钮中显示图标 58 2.3 ComboBox控件应用 59 实例054 将数据表中的字段添加到ComboBox控件 59 实例...