代码
1
/*
****** 导出到excel
*/
2
EXEC master..xp_cmdshell
'
bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""
'
3
4
/*
********** 导入Excel
*/
5
SELECT
*
6
FROM OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
'
,
7
'
Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
'
)...xactions
8
9
/*
动态文件名
10
declare @fn varchar(20),@s varchar(1000)
11
set @fn = 'c:\test.xls'
12
set @s ='''Microsoft.Jet.OLEDB.4.0'',
13
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
14
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
15
exec(@s)
16
*/
17
18
SELECT cast(cast(科目编号
as
numeric(
10
,
2
))
as
nvarchar(
255
))
+
'
'
转换后的别名
19
FROM OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
'
,
20
'
Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
'
)...xactions
21
22
/*
********************* EXCEL导到远程SQL
*/
23
insert OPENDATASOURCE(
24
'
SQLOLEDB
'
,
25
'
Data Source=远程ip;User ID=sa;Password=密码
'
26
).库名.dbo.表名 (列名1,列名2)
27
SELECT 列名1,列名2
28
FROM OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
'
,
29
'
Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0
'
)...xactions
30
31
32
/*
* 导入文本文件
*/
33
EXEC master..xp_cmdshell
'
bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword
'
34
35
/*
* 导出文本文件
*/
36
EXEC master..xp_cmdshell
'
bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword
'
37
或
38
EXEC master..xp_cmdshell
'
bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword
'
39
40
/*
*导出到TXT文本，用逗号分开
*/
41
exec master..xp_cmdshell
'
bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password
'
42
43
44
BULK INSERT 库名..表名
45
FROM
'
c:\test.txt
'
46
WITH (
47
FIELDTERMINATOR
=
'
;
'
,
48
ROWTERMINATOR
=
'
\n
'
49
)
50
51
52
--
/*
dBase IV文件
53
select * from
54
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
55
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
56
--
*/
57
58
--
/*
dBase III文件
59
select * from
60
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
61
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
62
--
*/
63
64
--
/*
FoxPro 数据库
65
select * from openrowset('MSDASQL',
66
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
67
'select * from [aa.DBF]')
68
--
*/
69
70
/*
*************导入DBF文件***************
*/
71
select
*
from openrowset(
'
MSDASQL
'
,
72
'
Driver=Microsoft Visual FoxPro Driver;
73
SourceDB
=
e:\VFP98\data;
74
SourceType
=
DBF
'
,
75
'
select * from customer where country != "USA" order by country
'
)
76
go
77
/*
**************** 导出到DBF **************
78
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
*/
79
80
insert into openrowset(
'
MSDASQL
'
,
81
'
Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\
'
,
82
'
select * from [aa.DBF]
'
)
83
select
*
from 表
84
85
/*
说明:
86
SourceDB=c:\ 指定foxpro表所在的文件夹
87
aa.DBF 指定foxpro表的文件名.
*/
88
89
90
/*
************导出到Access*******************
*/
91
insert into openrowset(
'
Microsoft.Jet.OLEDB.4.0
'
,
92
'
x:\A.mdb
'
;
'
admin
'
;
''
,A表) select
*
from 数据库名..B表
93
94
/*
************导入Access*******************
*/
95
insert into B表 selet
*
from openrowset(
'
Microsoft.Jet.OLEDB.4.0
'
,
96
'
x:\A.mdb
'
;
'
admin
'
;
''
,A表)
97
98
/*
文件名为参数
*/
99
declare @fname varchar(
20
)
100
set
@fname
=
'
d:\test.mdb
'
101
exec(
'
SELECT a.* FROM opendatasource(
''
Microsoft.Jet.OLEDB.4.0
''
,
102
'''
+@fname+
'''
;
''
admin
''
;
''''
, topics)
as
a
'
)
103
104
SELECT
*
105
FROM OpenDataSource(
'
Microsoft.Jet.OLEDB.4.0
'
,
106
'
Data Source="f:\northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;
'
)...产品
107
108
109
110
本文来自博客，转载请标明出处：http:
//
blog.csdn.net/fredrickhu/archive/2009/09/19/4569532.aspx
文章来源：http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569532.aspx
留着以备将来方便使用。呵呵。
转载于:https://www.cnblogs.com/angleSJW/archive/2010/03/16/1687267.html
