SQL語句導(dǎo)入導(dǎo)出大全

1、/* µ¼³öµ½excel /* µ¼ÈëExcel SELECT * 'Data Source="c:test.xls"User ID=Admin;Password=;Extended properties=Excel 5.0').xactions /*¶¯Ì¬ÎļþÃû declare fn varchar(20),s varchar(1000) set

2、 fn = 'c:test.xls' ''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 numer

3、ic(10,2) as nvarchar(255)+'¡¡' ת»»ºóµÄ±ðÃû 'Data Source="c:test.xls"User ID=Admin;Password=;Extended properties=Excel 5.0').xactions /* EXCELµ¼µ½Ô¶³ÌSQL insert OPE

4、NDATASOURCE( 'SQLOLEDB', 'Data Source=Ô¶³Ìip;User ID=sa;Password=ÃÜÂë' ).¿âÃû.dbo.±íÃû (ÁÐÃû1,ÁÐÃû2) SELECT ÁÐÃû1,ÁÐÃû2 '

5、;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' /* µ¼³

6、öÎı¾Îļþ 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' &

7、#181;¼³öµ½TXTÎı¾£¬ÓöººÅ·Ö¿ª exec master.xp_cmdshell 'bcp "¿âÃû.±íÃû" out "d:tt.txt" -c -t ,-U sa -P password' BULK INSERT

8、91;âÃû.±íÃû FROM 'c:test.txt' WITH ( FIELDTERMINATOR = '', ROWTERMINATOR = 'n' ) -/* dBase IVÎļþ select * from ,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:','select * from ¿Í»§×ÊÁ&#

9、207;4.dbf') -*/ -/* dBase IIIÎļþ select * from ,'dBase III;HDR=NO;IMEX=2;DATABASE=C:','select * from ¿Í»§×ÊÁÏ3.dbf') -*/ -/* FoxPro Êý¾Ý¿â select * from openrowset('MSDASQL', 'D

10、river=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select * from aa.DBF') -*/ /*µ¼ÈëDBFÎļþ*/ select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98data; SourceType=DBF', 'se

11、lect * from customer where country != "USA" order by country') go /* µ¼³öµ½DBF */ Èç¹ûÒªµ¼³öÊý¾Ýµ½ÒѾ­Éú³É½á¹¹(¼

12、;´ÏÖ´æµÄ)FOXPRO±íÖÐ,¿ÉÒÔÖ±½ÓÓÃÏÂÃæµÄSQLÓï¾ä insert into openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;S

13、ourceDB=c:', 'select * from aa.DBF') select * from ±í ˵Ã÷: SourceDB=c: Ö¸¶¨foxpro±íËùÔÚµÄÎļþ¼Ð aa.DBF Ö¸¶¨foxpro±íµÄÎ

14、6;¼þÃû. /*µ¼³öµ½Access*/ 'x:A.mdb''admin''',A±í) select * from Êý¾Ý¿âÃû.B±í /*µ¼ÈëAccess*/ 'x:A.mdb''admin''',A±í

15、;) ÎļþÃûΪ²ÎÊý declare fname varchar(20) set fname = 'd:test.mdb' '''+fname+'''''admin'''''', topics) as a ') SELECT * 'Data Source="f:northwind.mdb"Jet OLED

16、B:Database Password=123;User ID=Admin;Password=;').²úÆ· * µ¼Èë xml¡¡Îļþ DECLARE idoc int DECLARE doc varchar(1000) -sample XML document SET doc =' Customer was very satisfied Important Happy Customer. ' - Create an in

17、ternal 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_removedocum

18、ent idoc ? /*Excelµ¼µ½Txt*/ ÏëÓà select * into opendatasource(.) from opendatasource(.) ʵÏÖ½«Ò»¸öExcelÎļþÄÚÈݵ¼Èëµ½Ò»¸ö

19、;Îı¾Îļþ ¼ÙÉèExcelÖÐÓÐÁ½ÁУ¬µÚÒ»ÁÐΪÐÕÃû£¬µÚ¶þÁÐΪºÜÐÐÕ

20、;ʺÅ(16λ) ÇÒÒøÐÐÕʺŵ¼³öµ½Îı¾Îļþºó·ÖÁ½²¿·Ö£¬Ç°8λºÍºó

21、;8λ·Ö¿ª¡£ ×Þ½¡£º Èç¹ûÒªÓÃÄãÉÏÃæµÄÓï¾ä²åÈëµÄ»°,Îı¾Îļ&

22、#254;±ØÐë´æÔÚ,¶øÇÒÓÐÒ»ÐÐ:ÐÕÃû,ÒøÐÐÕ˺Å1,ÒøÐÐÕ˺Å2 È»ºó¾Í¿ÉÒÔ

23、ÓÃÏÂÃæµÄÓï¾ä½øÐвåÈë ×¢ÒâÎļþÃûºÍĿ¼¸ù¾ÝÄãµÄʵ¼ÊÇé

24、;¿ö½øÐÐÐÞ¸Ä. insert into ,'Text;HDR=Yes;DATABASE=C:' ).aa#txt -,aa#txt) -*/ select ÐÕÃû,ÒøÐÐÕ˺Å1=left(ÒøÐÐÕ˺Å,8),ÒøÐÐ

25、13;˺Å2=right(ÒøÐÐÕ˺Å,8) from ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls' -,Sheet1$) ).Sheet1$ Èç¹ûÄãÏëÖ±½Ó²åÈë²¢Éú³ÉÎ&#

26、196;±¾Îļþ,¾ÍÒªÓÃbcp declare sql varchar(8000),tbname varchar(50) -Ê×ÏȽ«excel±íÄÚÈݵ¼Èëµ½Ò»¸öÈ«¾ÖÁÙ

27、ʱ±í select tbname='#temp'+cast(newid() as varchar(40)+'' ,sql='select ÐÕÃû,ÒøÐÐÕ˺Å1=left(ÒøÐÐÕ˺Å,8),ÒøÐÐÕ˺Å2=right(&

28、#210;øÐÐÕ˺Å,8) into '+tbname+' from ,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls'' ).Sheet1$' exec(sql) -È»ºóÓÃbcp´ÓÈ«¾ÖÁÙʱ±íµ¼³&#

29、246;µ½Îı¾Îļþ set sql='bcp "'+tbname+'" out "c:aa.txt" /S"(local)" /P"" /c' exec master.xp_cmdshell sql -ɾ³ýÁÙʱ±í exec('drop table '+

30、tbname) /*µ¼Õû¸öÊý¾Ý¿â*/ ÓÃbcpʵÏֵĴ洢¹ý³Ì /* ʵÏÖÊý¾Ýµ¼Èë/µ¼³öµÄ

31、80;æ´¢¹ý³Ì ¸ù¾Ý²»Í¬µÄ²ÎÊý,¿ÉÒÔʵÏÖµ¼Èë/µ¼³öÕû¸öÊý¾Ý¿â/µ¥

32、;¸ö±í µ÷ÓÃʾÀý: -µ¼³öµ÷ÓÃʾÀý -µ¼³öµ¥¸ö±í exec file2table 'zj','','','xzkh_sa.µØÇø

33、×ÊÁÏ','c:zj.txt',1 -µ¼³öÕû¸öÊý¾Ý¿â exec file2table 'zj','','','xzkh_sa','C:docman',1 -µ¼Èëµ÷ÓÃʾÀý

34、 -µ¼Èëµ¥¸ö±í exec file2table 'zj','','','xzkh_sa.µØÇø×ÊÁÏ','c:zj.txt',0 -µ¼ÈëÕû¸öÊý¾Ý¿â exec file2ta

35、ble 'zj','','','xzkh_sa','C:docman',0 */ if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1) drop procedure File2Table go create procedure File2Table servername varchar(200) -·þÎ

36、1;Æ÷Ãû ,username varchar(200) -Óû§Ãû,Èç¹ûÓÃNTÑéÖ¤·½Ê½,ÔòΪ¿Õ'' ,password varchar(200) -ÃÜÂë ,tbname varchar(500) -Ê&

37、#253;¾Ý¿â.dbo.±íÃû,Èç¹û²»Ö¸¶¨:.dbo.±íÃû,Ôòµ¼³öÊý¾Ý¿âµÄËùÓÐÓû§±í ,file

38、name varchar(1000) -µ¼Èë/µ¼³ö·¾¶/ÎļþÃû,Èç¹ûtbname²ÎÊýÖ¸Ã÷Êǵ¼³öÕû¸öÊý¾Ý¿&

39、#226;,ÔòÕâ¸ö²ÎÊýÊÇÎļþ´æ·Å·¾¶,ÎļþÃû×Ô¶¯ÓñíÃû.txt ,isout bit -1Ϊµ¼³ö

40、;,0Ϊµ¼Èë as declare sql varchar(8000) if tbname like '%.%.%' -Èç¹ûÖ¸¶¨Á˱íÃû,ÔòÖ±½Óµ¼³öµ¥¸ö±í begin set sql=

41、9;bcp '+tbname +case when isout=1 then ' out ' else ' in ' end +' "'+filename+'" /w' +' /S '+servername +case when isnull(username,'')='' then '' else ' /U '+username end +' /P '+isnull(password,'')

42、 exec master.xp_cmdshell sql end else begin -µ¼³öÕû¸öÊý¾Ý¿â,¶¨ÒåÓαê,È¡³öËùÓеÄÓû§±í declare m_tbname varchar(250) if right(filename,1)<>'' set filename=filename+'' set m_tbname='declare #tb cursor for select name from '+tbname+'.sysobjects where xtype=''U''' exec(m_tbname) open #tb fetch next from #tb into m_tbname while fetch_statu