|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
|
1、在SQL数据库中直接从Excel里面查询数据：
select
*
from
OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0'
,
'Excel 5.0;HDR=YES;DATABASE=c:\test.xls'
,sheet1$)
2、从Excel文件中,导入数据到SQL数据库中,
select
*
into
表
from
OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0'
,
'Excel 5.0;HDR=YES;DATABASE=c:\test.xls'
,sheet1$)
3、从SQL数据库中,导出数据到Excel(excel存在),
insert
into
OPENROWSET(
'MICROSOFT.JET.OLEDB.4.0'
,
'Excel 5.0;HDR=YES;DATABASE=c:\test.xls'
,sheet1$)
select
*
from
表
4、从SQL数据库中,导出数据到Excel(excel不存在),
---- 导出表
EXEC
master..xp_cmdshell
'bcp 数据库名.dbo.表名 out "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
---- 导出查询语句
EXEC
master..xp_cmdshell
'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
5、导入导出的存储过程
--下面是导出真正Excel文件的方法:（请将一下所有代码复制到存储过程中）
if exists (
select
*
from
dbo.sysobjects
where
id = object_id(N
'[dbo].[p_exporttb]'
)
and
OBJECTPROPERTY(id, N
'IsProcedure'
) = 1)
drop
procedure
[dbo].[p_exporttb]
GO
/**//*
--数据导出EXCEL
导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/**//*
--调用示例
p_exporttb @tbname=
'地区资料'
,@path=
'c:'
,@fname=
'aa.xls'
--*/
create
proc p_exporttb
@tbname sysname,
--要导出的表名
@path nvarchar(1000),
--文件存放目录
@fname nvarchar(250)=
''
--文件名,默认为表名
as
declare
@err
int
,@src nvarchar(255),@
desc
nvarchar(255),@
out
int
declare
@obj
int
,@constr nvarchar(1000),@sql
varchar
(8000),@fdlist
varchar
(8000)
--参数检测
if
isnull
(@fname,
''
)=
''
set
@fname=@tbname+
'.xls'
--检查文件是否已经存在
if
right
(@path,1)<>
''
set
@path=@path+
''
create
table
#tb(a
bit
,b
bit
,c
bit
)
set
@sql=@path+@fname
insert
into
#tb
exec
master..xp_fileexist @sql
--数据库创建语句
set
@sql=@path+@fname
if exists(
select
1
from
#tb
where
a=1)
set
@constr=
'DRIVER={Microsoft Excel Driver (*.xls)};DSN='
''
';READONLY=FALSE'
+
';CREATE_DB="'
+@sql+
'";DBQ='
+@sql
else
set
@constr=
'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+
';DATABASE='
+@sql+
'"'
--连接数据库
exec
@err=sp_oacreate
'adodb.connection'
,@obj
out
if @err<>0
goto
lberr
exec
@err=sp_oamethod @obj,
'open'
,
null
,@constr
if @err<>0
goto
lberr
/**//*
--如果覆盖已经存在的表,就加上下面的语句
--创建之前先删除表/如果存在的话
select
@sql=
'drop table ['
+@tbname+
']'
exec
@err=sp_oamethod @obj,
'execute'
,@
out
out
,@sql
--*/
--创建表的SQL
select
@sql=
''
,@fdlist=
''
select
@fdlist=@fdlist+
',['
+a.
name
+
']'
,@sql=@sql+
',['
+a.
name
+
'] '
+
case
when
b.
name
like
'%char'
then
case
when
a.length>255
then
'memo'
else
'text('
+
cast
(a.length
as
varchar
)+
')'
end
when
b.
name
like
'%int'
or
b.
name
=
'bit'
then
'int'
when
b.
name
like
'%datetime'
then
'datetime'
when
b.
name
like
'%money'
then
'money'
when
b.
name
like
'%text'
then
'memo'
else
b.
name
end
FROM
syscolumns a
left
join
systypes b
on
a.xtype=b.xusertype
where
b.
name
not
in
(
'image'
,
'uniqueidentifier'
,
'sql_variant'
,
'varbinary'
,
'binary'
,
'timestamp'
)
and
object_id(@tbname)=id
select
@sql=
'create table ['
+@tbname
+
']('
+
substring
(@sql,2,8000)+
')'
,@fdlist=
substring
(@fdlist,2,8000)
exec
@err=sp_oamethod @obj,
'execute'
,@
out
out
,@sql
if @err<>0
goto
lberr
exec
@err=sp_oadestroy @obj
--导入数据
set
@sql=
'openrowset('
'MICROSOFT.JET.OLEDB.4.0'
','
'Excel 8.0;HDR=YES;IMEX=1
;DATABASE='
+@path+@fname+
''
',['
+@tbname+
'$])'
exec
(
'insert into '
+@sql+
'('
+@fdlist+
') select '
+@fdlist+
' from '
+@tbname)
return
lberr:
exec
sp_oageterrorinfo 0,@src
out
,@
desc
out
lbexit:
select
cast
(@err
as
varbinary(4))
as
错误号
,@src
as
错误源,@
desc
as
错误描述
select
@sql,@constr,@fdlist
go
if exists (
select
*
from
dbo.sysobjects
where
id = object_id(N
'[dbo].[p_exporttb]'
)
and
OBJECTPROPERTY(id, N
'IsProcedure'
) = 1)
drop
procedure
[dbo].[p_exporttb]
GO
/**//*
--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
如果文件不存在,将自动创建文件
如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/**//*
--调用示例
p_exporttb @sqlstr=
'select * from 地区资料'
,@path=
'c:'
,@fname=
'aa.xls'
,@sheetname=
'地区资料'
--*/
create
proc p_exporttb
@sqlstr
varchar
(8000),
--查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),
--文件存放目录
@fname nvarchar(250),
--文件名
@sheetname
varchar
(250)=
''
--要创建的工作表名,默认为文件名
as
declare
@err
int
,@src nvarchar(255),@
desc
nvarchar(255),@
out
int
declare
@obj
int
,@constr nvarchar(1000),@sql
varchar
(8000),@fdlist
varchar
(8000)
--参数检测
if
isnull
(@fname,
''
)=
''
set
@fname=
'temp.xls'
if
isnull
(@sheetname,
''
)=
''
set
@sheetname=
replace
(@fname,
'.'
,
'#'
)
--检查文件是否已经存在
if
right
(@path,1)<>
''
set
@path=@path+
''
create
table
#tb(a
bit
,b
bit
,c
bit
)
set
@sql=@path+@fname
insert
into
#tb
exec
master..xp_fileexist @sql
--数据库创建语句
set
@sql=@path+@fname
if exists(
select
1
from
#tb
where
a=1)
set
@constr=
'DRIVER={Microsoft Excel Driver (*.xls)};DSN='
''
';READONLY=FALSE'
+
';CREATE_DB="'
+@sql+
'";DBQ='
+@sql
else
set
@constr=
'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
+
';DATABASE='
+@sql+
'"'
--连接数据库
exec
@err=sp_oacreate
'adodb.connection'
,@obj
out
if @err<>0
goto
lberr
exec
@err=sp_oamethod @obj,
'open'
,
null
,@constr
if @err<>0
goto
lberr
--创建表的SQL
declare
@tbname sysname
set
@tbname=
'##tmp_'
+
convert
(
varchar
(38),newid())
set
@sql=
'select * into ['
+@tbname+
'] from('
+@sqlstr+
') a'
exec
(@sql)
select
@sql=
''
,@fdlist=
''
select
@fdlist=@fdlist+
',['
+a.
name
+
']'
,@sql=@sql+
',['
+a.
name
+
'] '
+
case
when
b.
name
like
'%char'
then
case
when
a.length>255
then
'memo'
else
'text('
+
cast
(a.length
as
varchar
)+
')'
end
when
b.
name
like
'%int'
or
b.
name
=
'bit'
then
'int'
when
b.
name
like
'%datetime'
then
'datetime'
when
b.
name
like
'%money'
then
'money'
when
b.
name
like
'%text'
then
'memo'
else
b.
name
end
FROM
tempdb..syscolumns a
left
join
tempdb..systypes b
on
a.xtype=b.xusertype
where
b.
name
not
in
(
'image'
,
'uniqueidentifier'
,
'sql_variant'
,
'varbinary'
,
'binary'
,
'timestamp'
)
and
a.id=(
select
id
from
tempdb..sysobjects
where
name
=@tbname)
if @@rowcount=0
return
select
@sql=
'create table ['
+@sheetname
+
']('
+
substring
(@sql,2,8000)+
')'
,@fdlist=
substring
(@fdlist,2,8000)
exec
@err=sp_oamethod @obj,
'execute'
,@
out
out
,@sql
if @err<>0
goto
lberr
exec
@err=sp_oadestroy @obj
--导入数据
set
@sql=
'openrowset('
'MICROSOFT.JET.OLEDB.4.0'
','
'Excel 8.0;HDR=YES
;DATABASE='
+@path+@fname+
''
',['
+@sheetname+
'$])'
exec
(
'insert into '
+@sql+
'('
+@fdlist+
') select '
+@fdlist+
' from ['
+@tbname+
']'
)
set
@sql=
'drop table ['
+@tbname+
']'
exec
(@sql)
return
lberr:
exec
sp_oageterrorinfo 0,@src
out
,@
desc
out
lbexit:
select
cast
(@err
as
varbinary(4))
as
错误号
,@src
as
错误源,@
desc
as
错误描述
select
@sql,@constr,@fdlist
go
本文转自问道博客51CTO博客，原文链接http://blog.51cto.com/450236/1842864如需转载请自行联系原作者 crackernet
|
通过SQL语句直接实现Excel与数据库的导入导出
2017-11-13
2815
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
目录
相关文章
|
5月前
|
SQL
开发框架
.NET
ASP.NET连接SQL数据库：详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步，ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中，我们经常需要与数据库进行交互，特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库，并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前，请确保您
407
3
3
|
1月前
|
SQL
存储
关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统（MySQL、PostgreSQL、Doris、Hive）的SQL优化策略。存储引擎特点、SQL执行流程及常见操作（如条件查询、排序、聚合函数）的优化方法。针对各数据库，索引使用、分区裁剪、谓词下推等技术，并提供了具体的SQL示例。通用的SQL调优技巧，如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略，可以显著提升数据库查询性能和系统稳定性。
86
9
10
|
2月前
|
SQL
Java
数据库连接
|
2月前
|
SQL
Java
数据库连接
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句？
大家好，我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库，可将 SQL 解析为 Java 对象树，支持多种 SQL 类型（如 `SELECT`、`INSERT` 等）。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后，可以方便地在 Java 代码中使用。
444
11
11
|
3月前
|
存储
Java
easyexcel
招行面试：100万级别数据的Excel，如何秒级导入到数据库？
本文由40岁老架构师尼恩撰写，分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备，在面试中展示强大的技术实力。针对百万级数据的Excel导入难题，尼恩推荐使用阿里巴巴开源的EasyExcel框架，并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案，实现高效的数据处理。此外，文章还提供了完整的代码示例和配置说明，帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题，提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
150
3
3
|
3月前
|
SQL
Oracle
数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导（SQL Access Advisor）的应用场景及其使用方法。访问指导通过分析给定的工作负载，提供索引、物化视图和分区等方面的优化建议，帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句，还能综合考虑多条SQL语句的优化效果，为DBA提供全面的决策支持。
106
11
11
|
3月前
|
SQL
关系型数据库
MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因，包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题，并提供了详细的解决步骤，如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等，确保导入过程顺利。
298
3
3
|
3月前
|
SQL
监控
安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制，旨在检测潜在威胁和漏洞，监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息，帮助管理员追踪数据库中的特定活动，确保数据安全和合规性。SQL Server审核分为服务器级和数据库级，涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警，帮助快速响应安全事件。
80
1
1
|
4月前
|
SQL
数据采集
监控
局域网监控电脑屏幕软件：PL/SQL 实现的数据库关联监控
在当今网络环境中，基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块，能够提供全面而准确的监控信息，帮助管理者有效监督局域网内的电脑使用情况。
69
2
2
|
4月前
|
SQL
Java
数据库连接
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
canal-starter 监听解析 storeValue 不一样，同样的sql 一个在mybatis执行 一个在数据库操作，导致解析不出正确对象
77
0
0
热门文章
最新文章
1
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
2
世界第一！阿里云PolarDB登顶全球数据库性能及性价比排行榜！
3
诚邀您参加《智能韧性·数智未来——云数据库RDS高可用与AI融合创新会议》线下活动！
4
如何解决 MySQL 数据库服务器 CPU 飙升的情况
5
世界第一！阿里云PolarDB刷新全球数据库性能及性价比记录
6
Navicat Premium 17 最新版下载与配置：5分钟完成企业级数据库工具部署
7
PolarDB开源数据库进阶课15 集成DeepSeek等大模型
8
刷新世界纪录！阿里云登顶全球数据库性能及性价比排行榜
9
数据库数据恢复—MongoDB数据库迁移过程中丢失文件的数据恢复案例
10
基于ssm的超市会员（积分)管理系统，附源码+数据库+论文，包安装调试
1
如何解决 SQL Server 占用内存过多问题
48
2
【YashanDB知识库】字段加上索引后，SQL查询不到结果
17
3
【YashanDB知识库】收集分区表统计信息采样率小于1导致SQL执行计划走偏
11
4
【YashanDB知识库】表收集统计信息默认阈值引起SQL执行效率差
15
5
玩转大数据：从零开始掌握SQL查询基础
72
6
SQL查询太慢？实战讲解YashanDB SQL调优思路
31
7
【YashanDB知识库】like 变量的SQL语句应用程序执行效率低与yasql执行效率高
13
8
win10 安装 sql server2012
26
9
阿里云DTS踩坑经验分享系列｜DTS打通SQL Server数据通道能力介绍
62
10
六、MyBatis特殊的SQL：模糊查询、动态设置表名、校验名称唯一性
24