SQL语句导入导出大全
1
作者：未知 来源：月光软件站 加入时间：2005-2-28 月光软件站
2
3SQL语句导入导出大全
4
/******* 导出到excel
5
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
6
7/*********** 导入Excel
8SELECT *
9FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
10'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')
xactions
11
12/*动态文件名
13declare @fn varchar(20),@s varchar(1000)
14set @fn = 'c:\test.xls'
15set @s ='''Microsoft.Jet.OLEDB.4.0'',
16''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
17set @s = 'SELECT * FROM OpenDataSource ('+@s+')sheet1$'
18exec(@s)
19
*/
20
21SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
22FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
23'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
24
25/********************** EXCEL导到远程SQL
26insert OPENDATASOURCE(
27'SQLOLEDB',
28'Data Source=远程ip;User ID=sa;Password=密码'
29).库名.dbo.表名 (列名1,列名2)
30SELECT 列名1,列名2
31FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
32'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
33
34
35/** 导入文本文件
36EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
37
38/** 导出文本文件
39EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
40或
41EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
42
43导出到TXT文本，用逗号分开
44exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
45
46
47BULK INSERT 库名..表名
48FROM 'c:\test.txt'
49WITH (
50FIELDTERMINATOR = ';',
51ROWTERMINATOR = '\n'
52)
53
54
55--/* dBase IV文件
56select * from
57OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
58,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
59--*/
60
61--/* dBase III文件
62select * from
63OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
64,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
65--*/
66
67--/* FoxPro 数据库
68select * from openrowset('MSDASQL',
69'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
70'select * from [aa.DBF]')
71--*/
72
73/**************导入DBF文件****************/
74select * from openrowset('MSDASQL',
75'Driver=Microsoft Visual FoxPro Driver;
76SourceDB=e:\VFP98\data;
77SourceType=DBF',
78'select * from customer where country != "USA" order by country')
79go
80/***************** 导出到DBF ***************/
81如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
82
83insert into openrowset('MSDASQL',
84'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
85'select * from [aa.DBF]')
86select * from 表
87
88说明:
89SourceDB=c:\ 指定foxpro表所在的文件夹
90aa.DBF 指定foxpro表的文件名.
91
92
93/*************导出到Access********************/
94insert into openrowset('Microsoft.Jet.OLEDB.4.0',
95'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表
96
97/*************导入Access********************/
98insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
99'x:\A.mdb';'admin';'',A表)
100
101文件名为参数
102declare @fname varchar(20)
103set @fname = 'd:\test.mdb'
104exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
105'''+@fname+''';''admin'';'''', topics) as a ')
106
107SELECT *
108FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
109'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')产品
110
111********************* 导入 xml 文件 [Page]
112
113DECLARE @idoc int
114DECLARE @doc varchar(1000)
115--sample XML document
116SET @doc ='
117<root>
118<Customer cid= "C1" name="Janine" city="Issaquah">
119<Order oid="O1" date="1/20/1996" amount="3.5" />
120<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
121</Order>
122</Customer>
123<Customer cid="C2" name="Ursula" city="Oelde" >
124<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
125white red">
126<Urgency>Important</Urgency>
127Happy Customer.
128</Order>
129<Order oid="O4" date="1/20/1996" amount="10000"/>
130</Customer>
131</root>
132'
133-- Create an internal representation of the XML document.
134EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
135
136-- Execute a SELECT statement using OPENXML rowset provider.
137SELECT *
138FROM OPENXML (@idoc, '/root/Customer/Order', 1)
139WITH (oid char(5),
140amount float,
141comment ntext 'text()')
142EXEC sp_xml_removedocument @idoc
143
144
145???????
146
147/**********************Excel导到Txt****************************************/
148想用
149select * into opendatasource() from opendatasource()
150实现将一个Excel文件内容导入到一个文本文件
151
152假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
153且银行帐号导出到文本文件后分两部分，前8位和后8位分开。
154
155
156邹健：
157如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
158然后就可以用下面的语句进行插入
159注意文件名和目录根据你的实际情况进行修改.
160
161insert into
162opendatasource('MICROSOFT.JET.OLEDB.4.0'
163,'Text;HDR=Yes;DATABASE=C:\'
164)[aa#txt]
165--,aa#txt)
166--*/
167select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
168from
169opendatasource('MICROSOFT.JET.OLEDB.4.0'
170,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
171--,Sheet1$)
172)[Sheet1$]
173
174
175如果你想直接插入并生成文本文件,就要用bcp
176
177declare @sql varchar(8000),@tbname varchar(50)
178
179--首先将excel表内容导入到一个全局临时表
180select @tbname='[##temp'+cast(newid() as varchar(40))+']'
181,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
182into '+@tbname+' from
183opendatasource(''MICROSOFT.JET.OLEDB.4.0''
184,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
185)[Sheet1$]'
186exec(@sql)
187
188--然后用bcp从全局临时表导出到文本文件
189set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
190exec master..xp_cmdshell @sql
191
192--删除临时表
193exec('drop table '+@tbname)
194
195
196/********************导整个数据库*********************************************/
197[Page]
198
199
200用bcp实现的存储过程
201
202
203/*
204实现数据导入/导出的存储过程
205根据不同的参数,可以实现导入/导出整个数据库/单个表
206调用示例:
207--导出调用示例
208----导出单个表
209exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
210----导出整个数据库
211exec file2table 'zj','','','xzkh_sa','C:\docman',1
212
213--导入调用示例
214----导入单个表
215exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
216----导入整个数据库
217exec file2table 'zj','','','xzkh_sa','C:\docman',0
218
219*/
220if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
221drop procedure File2Table
222go
223create procedure File2Table
224@servername varchar(200) --服务器名
225,@username varchar(200) --用户名,如果用NT验证方式,则为空''
226,@password varchar(200) --密码
227,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
228,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
229,@isout bit --1为导出,0为导入
230as
231declare @sql varchar(8000)
232
233if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
234begin
235set @sql='bcp '+@tbname
236+case when @isout=1 then ' out ' else ' in ' end
237+' "'+@filename+'" /w'
238+' /S '+@servername
239+case when isnull(@username,'')='' then '' else ' /U '+@username end
240+' /P '+isnull(@password,'')
241exec master..xp_cmdshell @sql
242end
243else
244begin --导出整个数据库,定义游标,取出所有的用户表
245declare @m_tbname varchar(250)
246if right(@filename,1)<>'\' set @filename=@filename+'\'
247
248set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
249exec(@m_tbname)
250open #tb
251fetch next from #tb into @m_tbname
252while @@fetch_status=0
253begin
254set @sql='bcp '+@tbname+'..'+@m_tbname
255+case when @isout=1 then ' out ' else ' in ' end
256+' "'+@filename+@m_tbname+'.txt " /w'
257+' /S '+@servername
258+case when isnull(@username,'')='' then '' else ' /U '+@username end
259+' /P '+isnull(@password,'')
260exec master..xp_cmdshell @sql
261fetch next from #tb into @m_tbname
262end
263close #tb
264deallocate #tb
265end
266go
267
268
269/************* Oracle **************/
270EXEC sp_addlinkedserver 'OracleSvr',
271'Oracle 7.3',
272'MSDAORA',
273'ORCLDB'
274GO
275
276delete from openquery(mailser,'select * from yulin')
277
278select * from openquery(mailser,'select * from yulin')
279
280update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888
281
282insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)
283
284
285补充：
286
287对于用bcp导出,是没有字段名的.
288
289用openrowset导出,需要事先建好表.
290
291用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入
292
293
294
作者：未知 来源：月光软件站 加入时间：2005-2-28 月光软件站2
3
SQL语句导入导出大全 4
/******* 导出到excel 
5
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""' 6
7
/*********** 导入Excel 8
SELECT * 9
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 10
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions 11
12
/*动态文件名 13
declare @fn varchar(20),@s varchar(1000) 14
set @fn = 'c:\test.xls' 15
set @s ='''Microsoft.Jet.OLEDB.4.0'', 16
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0''' 17
set @s = 'SELECT * FROM OpenDataSource ('+@s+')sheet1$' 18
exec(@s) 19
*/ 20
21
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名 22
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 23
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions 24
25
/********************** EXCEL导到远程SQL 26
insert OPENDATASOURCE( 27
'SQLOLEDB', 28
'Data Source=远程ip;User ID=sa;Password=密码' 29
).库名.dbo.表名 (列名1,列名2) 30
SELECT 列名1,列名2 31
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 32
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions 33
34
35
/** 导入文本文件 36
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword' 37
38
/** 导出文本文件 39
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword' 40
或 41
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword' 42
43
导出到TXT文本，用逗号分开 44
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password' 45
46
47
BULK INSERT 库名..表名 48
FROM 'c:\test.txt' 49
WITH ( 50
FIELDTERMINATOR = ';', 51
ROWTERMINATOR = '\n' 52
) 53
54
55
--/* dBase IV文件 56
select * from 57
OPENROWSET('MICROSOFT.JET.OLEDB.4.0' 58
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]') 59
--*/ 60
61
--/* dBase III文件 62
select * from 63
OPENROWSET('MICROSOFT.JET.OLEDB.4.0' 64
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]') 65
--*/ 66
67
--/* FoxPro 数据库 68
select * from openrowset('MSDASQL', 69
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 70
'select * from [aa.DBF]') 71
--*/ 72
73
/**************导入DBF文件****************/ 74
select * from openrowset('MSDASQL', 75
'Driver=Microsoft Visual FoxPro Driver; 76
SourceDB=e:\VFP98\data; 77
SourceType=DBF', 78
'select * from customer where country != "USA" order by country') 79
go 80
/***************** 导出到DBF ***************/ 81
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 82
83
insert into openrowset('MSDASQL', 84
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 85
'select * from [aa.DBF]') 86
select * from 表 87
88
说明: 89
SourceDB=c:\ 指定foxpro表所在的文件夹 90
aa.DBF 指定foxpro表的文件名. 91
92
93
/*************导出到Access********************/ 94
insert into openrowset('Microsoft.Jet.OLEDB.4.0', 95
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表 96
97
/*************导入Access********************/ 98
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 99
'x:\A.mdb';'admin';'',A表) 100
101
文件名为参数 102
declare @fname varchar(20) 103
set @fname = 'd:\test.mdb' 104
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'', 105
'''+@fname+''';''admin'';'''', topics) as a ') 106
107
SELECT * 108
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 109
'Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;')产品 110
111
********************* 导入 xml 文件 [Page]112
113
DECLARE @idoc int 114
DECLARE @doc varchar(1000) 115
--sample XML document 116
SET @doc =' 117
<root> 118
<Customer cid= "C1" name="Janine" city="Issaquah"> 119
<Order oid="O1" date="1/20/1996" amount="3.5" /> 120
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied 121
</Order> 122
</Customer> 123
<Customer cid="C2" name="Ursula" city="Oelde" > 124
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue 125
white red"> 126
<Urgency>Important</Urgency> 127
Happy Customer. 128
</Order> 129
<Order oid="O4" date="1/20/1996" amount="10000"/> 130
</Customer> 131
</root> 132
' 133
-- Create an internal representation of the XML document. 134
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 135
136
-- Execute a SELECT statement using OPENXML rowset provider. 137
SELECT * 138
FROM OPENXML (@idoc, '/root/Customer/Order', 1) 139
WITH (oid char(5), 140
amount float, 141
comment ntext 'text()') 142
EXEC sp_xml_removedocument @idoc 143
144
145
??????? 146
147
/**********************Excel导到Txt****************************************/ 148
想用 149
select * into opendatasource() from opendatasource() 150
实现将一个Excel文件内容导入到一个文本文件 151
152
假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位) 153
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。 154
155
156
邹健： 157
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2 158
然后就可以用下面的语句进行插入 159
注意文件名和目录根据你的实际情况进行修改. 160
161
insert into 162
opendatasource('MICROSOFT.JET.OLEDB.4.0' 163
,'Text;HDR=Yes;DATABASE=C:\' 164
)[aa#txt] 165
--,aa#txt) 166
--*/ 167
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) 168
from 169
opendatasource('MICROSOFT.JET.OLEDB.4.0' 170
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls' 171
--,Sheet1$) 172
)[Sheet1$] 173
174
175
如果你想直接插入并生成文本文件,就要用bcp 176
177
declare @sql varchar(8000),@tbname varchar(50) 178
179
--首先将excel表内容导入到一个全局临时表 180
select @tbname='[##temp'+cast(newid() as varchar(40))+']' 181
,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) 182
into '+@tbname+' from 183
opendatasource(''MICROSOFT.JET.OLEDB.4.0'' 184
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'' 185
)[Sheet1$]' 186
exec(@sql) 187
188
--然后用bcp从全局临时表导出到文本文件 189
set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c' 190
exec master..xp_cmdshell @sql 191
192
--删除临时表 193
exec('drop table '+@tbname) 194
195
196
/********************导整个数据库*********************************************/ 197
[Page]198
199
200
用bcp实现的存储过程 201
202
203
/* 204
实现数据导入/导出的存储过程 205
根据不同的参数,可以实现导入/导出整个数据库/单个表 206
调用示例: 207
--导出调用示例 208
----导出单个表 209
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1 210
----导出整个数据库 211
exec file2table 'zj','','','xzkh_sa','C:\docman',1 212
213
--导入调用示例 214
----导入单个表 215
exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0 216
----导入整个数据库 217
exec file2table 'zj','','','xzkh_sa','C:\docman',0 218
219
*/ 220
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1) 221
drop procedure File2Table 222
go 223
create procedure File2Table 224
@servername varchar(200) --服务器名 225
,@username varchar(200) --用户名,如果用NT验证方式,则为空'' 226
,@password varchar(200) --密码 227
,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表 228
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt 229
,@isout bit --1为导出,0为导入 230
as 231
declare @sql varchar(8000) 232
233
if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表 234
begin 235
set @sql='bcp '+@tbname 236
+case when @isout=1 then ' out ' else ' in ' end 237
+' "'+@filename+'" /w' 238
+' /S '+@servername 239
+case when isnull(@username,'')='' then '' else ' /U '+@username end 240
+' /P '+isnull(@password,'') 241
exec master..xp_cmdshell @sql 242
end 243
else 244
begin --导出整个数据库,定义游标,取出所有的用户表 245
declare @m_tbname varchar(250) 246
if right(@filename,1)<>'\' set @filename=@filename+'\' 247
248
set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U''' 249
exec(@m_tbname) 250
open #tb 251
fetch next from #tb into @m_tbname 252
while @@fetch_status=0 253
begin 254
set @sql='bcp '+@tbname+'..'+@m_tbname 255
+case when @isout=1 then ' out ' else ' in ' end 256
+' "'+@filename+@m_tbname+'.txt " /w' 257
+' /S '+@servername 258
+case when isnull(@username,'')='' then '' else ' /U '+@username end 259
+' /P '+isnull(@password,'') 260
exec master..xp_cmdshell @sql 261
fetch next from #tb into @m_tbname 262
end 263
close #tb 264
deallocate #tb 265
end 266
go 267
268
269
/************* Oracle **************/ 270
EXEC sp_addlinkedserver 'OracleSvr', 271
'Oracle 7.3', 272
'MSDAORA', 273
'ORCLDB' 274
GO 275
276
delete from openquery(mailser,'select * from yulin') 277
278
select * from openquery(mailser,'select * from yulin') 279
280
update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888 281
282
insert into openquery(mailser,'select disorder,catago from yulin')values(333,777) 283
284
285
补充： 286
287
对于用bcp导出,是没有字段名的. 288
289
用openrowset导出,需要事先建好表. 290
291
用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入 292
293
294
