先放一个成功的实例吧:下面是将一个数据库表导出到txt网络上的文件服务器上,将列用逗号分割(注意:尖括号内的东西都是要用实际的内容替代的),
其它的导入导出情况
--------------------------------------------------------------------------------
对excel 的操作
**********************************************************
1. 导出到excel :语法同上,只要把 -t 去掉就行了,因为默认的是以/t分割的
2. 从excel 导入sql 参见:在SQL Server 中查询Excel表
3. excel导到远程sql
insert opendatasource(
sqloledb,
data source=远程ip;user id=sa;password=密码
).库名.dbo.表名 (列名1,列名2)
select 列名1,列名2
from opendatasource( microsoft.jet.oledb.4.0,
data source="c:/test.xls";user id=admin;password=;extended properties=excel 5.0)...xactions
或者建立两个DB的关联,然后直接用[关联名称].[dbName].[dbo].[tableName]访问远程数据库
对txt文档的操作
****************************************************
/** 导入文本文件
exec master..xp_cmdshell 'bcp dbname..tablename in c:/dt.txt -c -sservername -usa -ppassword'
/** 导出文本文件
exec master..xp_cmdshell 'bcp dbname..tablename out c:/dt.txt -c -sservername -usa -ppassword'
或
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:/dt.txt -c -sservername -usa -ppassword '
BULK INSERT tbsmakt
FROM '//fs01/Temp/Neil/makt.txt'
WITH (
FIELDTERMINATOR = '||',
ROWTERMINATOR = '/n'
)
--/* dbase iv文件
select * from
openrowset(microsoft.jet.oledb.4.0
,dbase iv;hdr=no;imex=2;database=c:/,select * from [客户资料4.dbf])
--*/
--/* dbase iii文件
select * from
openrowset(microsoft.jet.oledb.4.0
,dbase iii;hdr=no;imex=2;database=c:/,select * from [客户资料3.dbf])
--*/
--/* foxpro 数据库
select * from openrowset(msdasql,
driver=microsoft visual foxpro driver;sourcetype=dbf;sourcedb=c:/,
select * from [aa.dbf])
--*/
/**************导入dbf文件****************/
select * from openrowset(msdasql,
driver=microsoft visual foxpro driver;
sourcedb=e:/vfp98/data;
sourcetype=dbf,
select * from customer where country != "usa" order by country)
go
/***************** 导出到dbf ***************/
如果要导出数据到已经生成结构(即现存的)foxpro表中,可以直接用下面的sql语句
insert into openrowset(msdasql,
driver=microsoft visual foxpro driver;sourcetype=dbf;sourcedb=c:/,
select * from [aa.dbf])
select * from 表
说明:
sourcedb=c:/ 指定foxpro表所在的文件夹
aa.dbf 指定foxpro表的文件名.
//**************access数据库
seelct * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:/Program Files/Microsoft Office/OFFICE11/SAMPLES/Northwind.mdb';'admin';'', Orders)
Orders------------TableName
文件名为参数
declare @fname varchar(20)
set @fname = d:/test.mdb
exec(select a.* from opendatasource(microsoft.jet.oledb.4.0,
+@fname+;admin;, topics) as a )
select *
from opendatasource( microsoft.jet.oledb.4.0,
data source="f:/northwind.mdb";jet oledb:database password=123;user id=admin;password=;)...产品
********************* 导入 xml 文件
declare @idoc int
declare @doc varchar(1000)
--sample xml document．br> set @doc =
< root>
< customer cid= "c1" name="janine" city="issaquah">
< order oid="o1" date="1/20/1996" amount="3.5" />
< order oid="o2" date="4/30/1997" amount="13.4"> customer was very satisfied
< /order>
< /customer>
< customer cid="c2" name="ursula" city="oelde" >
< order oid="o3" date="7/14/1999" amount="100" note="wrap it blue
white red">
< urgency> important< /urgency>
happy customer.
< /order>
< order oid="o4" date="1/20/1996" amount="10000"/>
< /customer>
< /root>
-- create an internal representation of the xml document．
exec sp_xml_preparedocument．nbsp@idoc output, @doc
-- execute a select statement using openxml rowset provider.
select *
from openxml (@idoc, /root/customer/order, 1)
with (oid char(5),
amount float,
comment ntext text())
exec sp_xml_removedocument．nbsp@idoc