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

/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:\test.xls'
set @s ='''microsoft.jet.oledb.4.0'',
''data source="'+@fn+'";user id=admin;password=;extended properties=excel 5.0'''
set @s = 'select * from opendatasource ('+@s+')...sheet1$'
exec(@s)
*/

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

/********************** 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


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