SQL Server SQL语句导入导出大全

/*******  导出到excel
exec master..xp_cmdshell ’bcp settledb.dbo.shanghu out c:/temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""’

/***********  导入excel
select *
from opendatasource( ’microsoft.jet.oledb.4.0’,
  ’data source="c:/test.xls";user id=admin;password=;extended properties=excel 5.0’)...xactions

select cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’　’ 转换后的别名
from opendatasource( ’microsoft.jet.oledb.4.0’,
  ’data source="c:/test.xls";user id=admin;password=;extended properties=excel 5.0’)...xactions

/** 导入文本文件
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’

导出到txt文本，用逗号分开
exec master..xp_cmdshell ’bcp "库名..表名" out "d:/tt.txt" -c -t ,-u sa -p password’

bulk insert 库名..表名
from ’c:/test.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********************/
insert into openrowset(’microsoft.jet.oledb.4.0’,
   ’x:/a.mdb’;’admin’;’’,a表) select * from 数据库名..b表

/*************导入access********************/
insert into b表 selet * from openrowset(’microsoft.jet.oledb.4.0’,
   ’x:/a.mdb’;’admin’;’’,a表)

*********************  导入 xml　文件

declare @idoc int
declare @doc varchar(1000)
--sample xml document
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 @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 @idoc