SQL SERVER ÓëACCESS¡¢EXCELµÄÊý¾Ýת»»
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØʵÏÖSQL SERVER¡¢ACCESS¡¢EXCELÊý¾Ýת»»£¬Ïêϸ˵Ã÷ÈçÏ£º
Ò»¡¢ SQL SERVER ºÍACCESSµÄÊý¾Ýµ¼Èëµ¼³ö
³£¹æµÄÊý¾Ýµ¼Èëµ¼³ö£º
ʹÓÃDTSÏòµ¼Ç¨ÒÆÄãµÄAccessÊý¾Ýµ½SQL Server£¬Äã¿ÉÒÔʹÓÃÕâЩ²½Öè:
¡¡¡¡1ÔÚSQL SERVERÆóÒµ¹ÜÀíÆ÷ÖеÄTools£¨¹¤¾ß£©²Ëµ¥ÉÏ£¬Ñ¡ÔñData Transformation
¡¡¡¡2Services£¨Êý¾Ýת»»·þÎñ£©£¬È»ºóÑ¡Ôñ czdImport Data£¨µ¼ÈëÊý¾Ý£©¡£
¡¡¡¡3ÔÚChoose a Data Source£¨Ñ¡ÔñÊý¾ÝÔ´£©¶Ô»°¿òÖÐÑ¡ÔñMicrosoft Access as the Source£¬È»ºó¼üÈëÄãµÄ.mdbÊý¾Ý¿â(.mdbÎļþÀ©Õ¹Ãû)µÄÎļþÃû»òͨ¹ýä¯ÀÀÑ°ÕÒ¸ÃÎļþ¡£
¡¡¡¡4ÔÚChoose a Destination£¨Ñ¡ÔñÄ¿±ê£©¶Ô»°¿òÖУ¬Ñ¡ÔñMicrosoft OLE¡¡DB Prov ider for SQL¡¡Server£¬Ñ¡ÔñÊý¾Ý¿â·þÎñÆ÷£¬È»ºóµ¥»÷±ØÒªµÄÑéÖ¤·½Ê½¡£
¡¡¡¡5ÔÚSpecify Table Copy£¨Ö¸¶¨±í¸ñ¸´ÖÆ£©»òQuery£¨²éѯ£©¶Ô»°¿òÖУ¬µ¥»÷Copy tables£¨¸´ÖƱí¸ñ£©¡£
6ÔÚSelect Source Tables£¨Ñ¡ÔñÔ´±í¸ñ£©¶Ô»°¿òÖУ¬µ¥»÷Select All£¨È«²¿Ñ¡¶¨£©¡£ÏÂÒ»²½£¬Íê³É¡£
Transact-SQLÓï¾ä½øÐе¼Èëµ¼³ö£º
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éѯµÄʾÀý£¬Ëüͨ¹ýÓÃÓÚ Jet µÄ OLE DB Ìṩ³ÌÐò²éѯ Excel µç×Ó±í¸ñ¡£
SELECT *
from OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------
2¡¢½«ExcelµÄÊý¾Ýµ¼ÈëSQL server :
-- ======================================================
SELECT * into newtable
from OpenDataSource( 'Mic
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......
½ñÌì½Óµ½¿Í»§µç»°£¬Ëµ²Ù×÷Êý¾ÝÎÞ·¨±£´æ¡£¾¹ý·ÖÎö£¬·¢ÏÖËûµÄÊý¾Ý¿âÒѾÓÐ5G¶àµÄ´óС£¬¶ø×îÖÕ·¢ÏÖÓÐÕűíµÄË÷Òý³ö´íÁË£¬ÓÃDBCC CHECKÒ²ÎÞÁ¦»ØÌì¡£
ÿ´ÎÓÃselect * from ln003082 Óï¾ä²éѯ£¬¶¼±¨ÈçÏ´íÎó£º
·þÎñÆ÷: ÏûÏ¢ 605£¬¼¶±ð 21£¬×´Ì¬ 1£¬ÐÐ 1
ÊÔͼ´ÓÊý¾Ý¿â 'ln_fl0125' ÖÐÌáÈ¡µÄÂß¼ ......
×î½ü×öÏîÄ¿£¬ÐèÒªÔ¶³Ì·ÃÎÊÊý¾Ý¿â£¬×Ô¼ºÔÚ²éÁËЩ×ÊÁÏ£¬×ܽáÁËһϣ¬Ï£Íû¶Ô´ó¼ÒÓаïÖú£º
×÷Õߣºshinehoo
Ò»¡¢ÅäÖÃSQL Server 2005
1£©°²×°SQL Server 2005¿ª·¢°æ£»
2£©¿ªÊ¼->³ÌÐò->Microsoft SQL Server 2005->SQL
Server 2005ÍâΧӦÓÃÅäÖÃÆ÷£¬ÔÚ´ò¿ªµÄ½çÃæµ¥»÷“·þÎñµÄÁ¬½ÓµÄÍâΧӦÓÃÅäÖÃÆ÷”£¬ÔÚ´ò ......