sql导入导出大全2008年07月01日星期二下午03:12SELECT*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\aaaa.xls";UserID=Admin;Password=;Extendedproperties=Excel11.0')...[Sheet1$]/*******导出到excelexecmaster..xp_cmdshell'bcpsettledb.dbo.shanghuoutc:\temp1.xls-c-q-s"gnetdata/gnetdata"-u"sa"-p""'/***********导入excelselect*fromopendatasource('microsoft.jet.oledb.4.0','datasource="c:\test.xls";userid=admin;password=;extendedproperties=excel5.0')...xactions/*动态文件名declare@fnvarchar(20),@svarchar(1000)set@fn='c:\test.xls'set@s='''microsoft.jet.oledb.4.0'',''datasource="'+@fn+'";userid=admin;password=;extendedproperties=excel5.0'''set@s='select*fromopendatasource('+@s+')...sheet1$'exec(@s)*/selectcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+''转换后的别名fromopendatasource('microsoft.jet.oledb.4.0','datasource="c:\test.xls";userid=admin;password=;extendedproperties=excel5.0')...xactions/**********************excel导到远程sqlinsertopendatasource('sqloledb','datasource=远程ip;userid=sa;password=密码').库名.dbo.表名(列名1,列名2)select列名1,列名2fromopendatasource('microsoft.jet.oledb.4.0','datasource="c:\test.xls";userid=admin;password=;extendedproperties=excel5.0')...xactions/**导入文本文件execmaster..xp_cmdshell'bcpdbname..tablenameinc:\dt.txt-c-sservername-usa-ppassword'/**导出文本文件execmaster..xp_cmdshell'bcpdbname..tablenameoutc:\dt.txt-c-sservername-usa-ppassword'或execmaster..xp_cmdshell'bcp"select*fromdbname..tablename"queryoutc:\dt.txt-c-sservername-usa-ppassword'导出到txt文本，用逗号分开execmaster..xp_cmdshell'bcp"库名..表名"out"d:\tt.txt"-c-t,-usa-ppassword'bulkinsert库名..表名from'c:\test.txt'with(fieldterminator=';',rowterminator='\n')--/*dbaseiv文件select*fromopenrowset('microsoft.jet.oledb.4.0','dbaseiv;hdr=no;imex=2;database=c:\','select*from[客户资料4.dbf]')--*/--/*dbaseiii文件select*fromopenrowset('microsoft.jet.oledb.4.0','dbaseiii;hdr=no;imex=2;database=c:\','select*from[客户资料3.dbf]')--*/--/*foxpro数据库select*fromopenrowset('msdasql','driver=microsoftvisualfoxprodriver;sourcetype=dbf;sourcedb=c:\','select*from[aa.d