资源描述:
《sql语句导入导出大全》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、SQL语句导入导出大全[收集]正巧这两天要用到这个,上网找到一个,回来自己检测一下:/*******导出到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;P
2、assword=;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+')...
3、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导到远程SQLinsertOPENDATASOUR
4、CE('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..tab
5、lenameinc: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文本,用逗号分开execmaste
6、r..xp_cmdshell'bcp"库名..表名"out"d:tt.txt"-c-t,-Usa-Ppassword'BULKINSERT库名..表名FROM'c:test.txt'WITH(FIELDTERMINATOR=';',ROWTERMINATOR='')--/*dBaseIV文件select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBaseIV;HDR=NO;IMEX=2;DATABASE=C:','select*from[客户资料4.dbf]')--*/--/
7、*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.DBF]')--*//****
8、**********导入DBF文件****************/select*fromopenrowset('MSDASQL','Driver=MicrosoftVisualFoxProDriver;SourceDB=e:VFP98data;Source