|
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
2446
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
目录
相关文章
|
28天前
|
SQL
开发框架
.NET
ASP.NET连接SQL数据库：详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步，ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中，我们经常需要与数据库进行交互，特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库，并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前，请确保您
132
3
3
|
11天前
|
SQL
数据采集
监控
局域网监控电脑屏幕软件：PL/SQL 实现的数据库关联监控
在当今网络环境中，基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块，能够提供全面而准确的监控信息，帮助管理者有效监督局域网内的电脑使用情况。
13
2
2
|
16天前
|
SQL
XML
Java
excel转sql小工具
该工具用于将Excel数据转换为SQL INSERT语句，便于历史数据迁移到新数据库。通过配置文件定义Excel表头与数据库字段的映射关系，并支持默认值设置及spEL表达式。主要依赖包括EasyExcel读取Excel，以及Lombok、Hutool等辅助工具。项目包含`Excel2SqlUtils.java`和`Excel2SqlListener.java`两个核心类，前者负责加载配置文件，后者实现数据读取与SQL语句生成。配置文件`model.yml`定义了具体的映射规则。
18
1
1
|
30天前
|
SQL
关系型数据库
MySQL
|
29天前
|
SQL
监控
数据库
|
29天前
|
SQL
数据库
数据库管理
|
1月前
|
SQL
存储
数据可视化
SQL 数据库大揭秘：连接数字世界的魔法桥梁
在数字化时代，数据如繁星般璀璨，而 SQL 数据库则像强大的引力场，有序汇聚、整理和分析这些数据。SQL 数据库是一个巨大的数字宝库，装满各行各业的“宝藏”。本文将带你探索 SQL 数据库在电商、金融、医疗和教育等领域的应用。例如，在电商中，它能精准推荐商品；在金融中，它是安全卫士，防范欺诈；在医疗中，它是健康管家，管理病历；在教育中，则是智慧导师，个性化教学。此外，还将介绍如何利用板栗看板等工具实现数据可视化，提升决策效率。
34
4
4
|
1月前
|
SQL
监控
数据处理
SQL数据库数据修改操作详解
数据库是现代信息系统的重要组成部分，其中SQL（StructuredQueryLanguage）是管理和处理数据库的重要工具之一。在日常的业务运营过程中，数据的准确性和及时性对企业来说至关重要，这就需要掌握如何在数据库中正确地进行数据修改操作。本文将详细介绍在SQL数据库中如何修改数据，帮助读者更好
149
4
4
|
1月前
|
SQL
存储
Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代，数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一，广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
46
3
3
|
1月前
|
SQL
数据管理
数据库
文章初学者指南：SQL新建数据库详细步骤与最佳实践
引言：在当今数字化的世界，数据库管理已经成为信息技术领域中不可或缺的一部分。作为广泛使用的数据库管理系统，SQL已经成为数据管理和信息检索的标准语言。本文将详细介绍如何使用SQL新建数据库，包括准备工作、具体步骤和最佳实践，帮助初学者快速上手。一、准备工作在开始新建数据库之前，你需要做好以下准备工作
109
3
3