SQL_SERVER导出.docx

/******* 导出到excelexec master..xp_cmdshell bcp settledb.dbo.shanghu out c:＼temp1.xls -c -q -sgnetdata/gnetdata -usa -p/*********** 导入excelselect * 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.xlsset @s =microsoft.jet.oledb.4.0,data source=+@fn+;user id=admin;password=;extended properties=excel 5.0set @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导到远程sqlinsert opendatasource(sqloledb,data source=远程ip;user id=sa;password=密码).库名.dbo.表名 (列名1,列名2)select 列名1,列名2from 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 passwordbulk insert 库名..表名from c:＼test.txtwith (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:＼vfpA