SQL¶ÁÈ¡EXCEL
Ö±½ÓÔÚSQL²éѯ·ÖÎöÆ÷ÖжÁÈ¡EXCELÎļþÐèҪʹÓõ½OPENDATASOURCE¡£
µ«ÊÇʹÓÃËü֮ǰÐèÒª½øÐÐÅäÖÃһϡ£¼ÇµÃÈçÏÂÅäÖÃÊDZØÐëµÄ£º
1¡¢Ö´ÐÐÕâÁ½¸ö´æ´¢¹ý³Ì£º
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
ËüµÄ×÷Óãº
µÚÒ»¸öÊÇ£ºÊÇ·ñÖ§³Ö¸ß¼¶Ñ¡ÏîµÄ£¬1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
µÚ¶þ¸öÊÇ£ºÊÇ·ñÖ§³Ö·Ö²¼Ê½²éѯ£¬
1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
¶øÇÒÒ»¶¨ÊÇÏÈÖ§³Ö¸ß¼¶Ñ¡ÏÔÙ¿ÉÒÔÉèÖ÷ֲ¼Ê½²éѯ£¬ÒòΪ·Ö²¼Ê½²éѯ±¾Éí¾ÍÊÇ
Ò»¸ö¸ß¼¶Ñ¡ÏîÀ´µÄ¡£
2¡¢Ê¹ÓÃOPENDATASOURCE£¬ËüÓÐÁ½ÖÖÓï·¨
£¨1£©SELECT * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\TempExcelData\Exl_Test_01.xls;Extended Properties=EXCEL 5.0')...[Student$]
£¨2£©select *
from Openrowset('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;
DATABASE=D:\TempExcelData\Exl_Test_01.xls', Student$)
3¡¢ÓÃÍêºóÒª¹Ø±ÕµÚÒ»²½´ò¿ªµÄ¶«Î÷
exec sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
°´Õմ˲½²»Ò»¶¨¿ÉÒÔ²éѯµ½ÄãEXCELµÄÊý¾Ý£¬¿ÉÄÜ»¹»áÓÐÆäËüµÄ´íÎ󣬱ÈÈç˵ȨÏÞ
²»×ã¹»Òý·¢ÆäËüµÄÎÊÌâ°¡£¬ÒªÔÚÍøÉÏÕÒ¶àһϣ¬¾ÍÈçÎÒÅäÖõÄʱºò£¬ÓõÄÊÇSA½øÈ¥µÄ£¬
µ«ÊÇSA²»ÊÇSYSADMINISTRATORÕâÒ»×飬Ҫ¼Ó½øÈ¥ºó²ÅÓÐȨÏÞ£¬²ÅÄÜ˳Àû²éµ½Êý¾Ý
Ïà¹ØÎĵµ£º
Transact SQL Óï ¾ä ¹¦ ÄÜ
========================================================================
¡¡¡¡--Êý¾Ý²Ù×÷
¡¡¡¡ SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁÐ
¡¡¡¡¡¡¡¡¡¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐÐ
¡¡¡¡¡¡¡¡¡¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐÐ
¡¡¡¡¡¡¡¡¡¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý ......
1ÏÞÖÆ SQL Server·þÎñµÄȨÏÞ
¡¡¡¡
¡¡¡¡SQL Server 2000 ºÍ SQL Server Agent ÊÇ×÷Ϊ Windows ·þÎñÔËÐеġ£Ã¿¸ö·þÎñ±ØÐëÓëÒ»¸ö Windows ÕÊ»§Ïà¹ØÁª£¬²¢´ÓÕâ¸öÕÊ»§ÖÐÑÜÉú³ö°²È«ÐÔÉÏÏÂÎÄ¡£SQL ServerÔÊÐísa µÇ¼µÄÓû§£¨ÓÐʱҲ°üÀ¨ÆäËûÓû§£©À´·ÃÎʲÙ×÷ÏµÍ³ÌØÐÔ¡£ÕâЩ²Ù×÷ϵͳµ÷ÓÃÊÇÓÉÓµÓзþÎñÆ÷½ø³ÌµÄÕÊ»§µÄ°²È«ÐÔÉÏÏÂÎÄÀ´´ ......
µ÷ÊÔSQLÊý¾Ý£¬·¢ÏÖÊý¾Ý¼Ç¼¼¯Öظ´ÎÊÌ⣬ËùÒÔ£¬¼ÆËã³öµÄÊý¾Ý½á¹û±¶ÊýÎÊÌ⡣ͨ¹ýµ÷ÊÔSQL£¬·¢ÏÖÊÇÎïÁϵķÖÀà²úÉúÖØ¸´£»Ö®ËùÒÔ²úÉúÖØ¸´£¬ÎïÁϵķÖÀà±ê×¼²»Ò»Ñù£¬Óëʵ¼ÊµÄÒµÎñÓйء£³ÌÐòÖÐÒ»Ö±ÓÃÀà±ðÀ´Çø·ÖÀà±ð£¬¶øÕâÕÅ´Îʵ¼ÊÒµÎñ²»ÐèÒªÓëÀà±ðÓйأ¬ËùÒÔ£¬Ã»ÓжÔÓ¦µÄ¹ýÂËÌõ¼þ£¬ËùÓеÄÀà±ðÈ«²¿Ñ¡³öÀ´ÁË¡£È»ºó£¬°ÑÏÂÃæµÄºìÉ«×Ö¶Î×¢ ......
±¾ÎÄ×ªÔØÓÚ£ºhttp://www.javaeye.com/topic/185385
ѧϰÊý¾Ý¿â²éѯµÄʱºò¶Ô¶à±íÁ¬½Ó²éѯµÄÓÐЩ¸ÅÄ±È½ÏÄ£ºý¡£¶øÁ¬½Ó²éѯÊÇÔÚÊý¾Ý¿â²éѯ²Ù×÷µÄʱºò¿Ï¶¨ÒªÓõ½µÄ¡£¶ÔÓڴ˸ÅÄî
ÎÒÓÃͨË×һЩµÄÓïÑÔºÍÀý×ÓÀ´½øÐн²½â¡£Õâ¸öÀý×ÓÊÇÎÒ½²¿ÎµÄʱºò¾³£²ÉÓõÄÀý×Ó¡£
Ê×ÏÈÎÒÃÇ×öÁ½ÕÅ±í£ºÔ±¹¤ÐÅÏ¢±íºÍ²¿ÃÅÐÅÏ¢±í£¬ÔÚ´Ë£¬±íµ ......