|
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
1975
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
目录
相关文章
|
12天前
|
SQL
人工智能
算法
|
29天前
|
SQL
存储
BI
|
12天前
|
SQL
算法
数据库
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
74
6
7
|
1天前
|
SQL
Oracle
关系型数据库
sql语句创建数据库
在创建数据库之前，请确保你有足够的权限，并且已经考虑了数据库的安全性和性能需求。此外，不同的DBMS可能有特定的最佳实践和配置要求，因此建议查阅相关DBMS的官方文档以获取更详细和准确的信息。
17
4
5
|
1天前
|
SQL
缓存
数据库
sql 数据库优化
SQL数据库优化是一个复杂且关键的过程，涉及多个层面的技术和策略。以下是一些主要的优化建议：
查询语句优化：
避免全表扫描：在查询时，尽量使用索引来减少全表扫描，提高查询速度。
使用合适的子查询方式：子查询可能降低查询效率，但可以通过优化子查询的结构或使用连接（JOIN）替代子查询来提高性能。
简化查询语句：避免不必要的复杂查询，尽量使SQL语句简单明了。
使用EXISTS替代IN：在查询数据是否存在时，使用EXISTS通常比IN更快。
索引优化：
建立合适的索引：对于经常查询的列，如主键和外键，应创建相应的索引。同时，考虑使用覆盖索引来进一步提高性能。
避免过多的索引：虽然索引可以提高查询
33
2
2
|
8天前
|
SQL
数据库
|
9天前
|
SQL
关系型数据库
数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
33
3
3
|
11天前
|
SQL
监控
数据库
数据库管理与电脑监控软件：SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化，如使用索引和调整查询语句，能有效提高数据库性能。同时，合理设计数据库结构，如数据表划分和规范化，也能增强管理效率。此外，利用Python脚本自动化收集系统性能数据，并实时提交至网站，可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性，满足用户需求。
41
0
0
|
12天前
|
SQL
存储
数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境：
一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5，划分LUN供这台服务器使用。
在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。
服务器故障&初检：
由于未知原因，Sql Server数据库文件丢失，丢失数据涉及到3个库，表的数量有3000左右。数据库文件丢失原因还没有查清楚，也不能确定数据存储位置。
数据库文件丢失后服务器仍处于开机状态，所幸没有大量数据写入。
将raid5中所有磁盘编号后取出，经过硬件工程师检测，没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像，镜像完成后将所
20
1
1
|
20天前
|
数据库
SQL
索引
热门文章
最新文章
1
面试官问10W 行级别数据的 Excel 导入如何10秒处理
2
Excel轻松操控：掌握EasyPoi的妙用技巧（一）
3
一键实现数据采集和存储：Python爬虫、Pandas和Excel的应用技巧
4
Excel轻松操控：掌握EasyPoi的妙用技巧（二）
5
C#实现Excel合并单元格数据导入数据集
6
【办公自动化】Excel透视表的简单应用
7
使用Python批量实现文件夹下所有Excel文件的第二张表合并
8
一文搞懂SQL优化——如何高效添加数据
9
python使用tkinter库，封装操作excel为GUI程序
10
【力扣】168. Excel表列名称、171. Excel 表列序号
1
Javaweb之数据库连接池以及lombok类库的详细解析
9
2
数据库开发之图形化工具以及表操作的详细解析
13
3
数据库恢复挂起之更换数据库
8
4
数据库开发之mysql前言以及详细解析
9
5
「杭州*康恩贝」4月26日PolarDB开源数据库沙龙，开启报名！
8
6
Python与NoSQL数据库（MongoDB、Redis等）面试问答
34
7
Python与MySQL数据库交互：面试实战
25
8
《MySQL 简易速速上手小册》第6章：MySQL 复制和分布式数据库（2024 最新版）
31
9
寻找最懂数据库的你，开启云端推广事业！丰厚收益，轻松赚取！
29
10
❤Nodejs 第六章（操作本地数据库前置知识优化）
14