¡¡¡¡Ò»¡¢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Àï²éѯaccessÊý¾Ý:

SELECT *

FROM OpenDataSource£¨ Microsoft.Jet.OLEDB.4.0,

Data Source="c:\DB.mdb";User ID=Admin;Password=£©...±íÃû

¡¡¡¡2. ½«accessµ¼ÈëSQL server

ÔÚSQL SERVER ÀïÔËÐÐ:

SELECT *

INTO newtable

FROM OPENDATASOURCE £¨Microsoft.Jet.OLEDB.4.0,

Data Source="c:\DB.mdb";User ID=Admin;Password= £©...±íÃû

¡¡¡¡3. ½«SQL SERVER±íÀïµÄÊý¾Ý²åÈëµ½Access±íÖÐ

¡¡¡¡ÔÚSQL SERVER ÀïÔËÐУº

insert into OpenDataSource£¨ Microsoft.Jet.OLEDB.4.0,

Data Source=" c:\DB.mdb";User ID=Admin;Password=£©...±íÃû

£¨ÁÐÃû1,ÁÐÃû2£©

select ÁÐÃû1,ÁÐÃû2 from sql±í

ʵÀý£º

insert into OPENROWSET£¨Microsoft.Jet.OLEDB.4.0,

C:\db.mdb;admin;, Test£©

select id,name from Test

INSERT INTO OPENROWSET£¨Microsoft.Jet.OLEDB.4.0, c:\trade.mdb; admin; , ±íÃû£©

SELECT *

FROM sqltablename

¶þ¡¢ SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö

¡¡¡¡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£¨ Microsoft.Jet.OLEDB.4.0,

Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0£©...[Sheet1$]

ʵÀý:

SELECT * into newtable

FROM OpenDataSource£¨ Microsoft.Jet.OLEDB.4.0,

Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0£©...xactions

¡¡¡¡3¡¢½«SQL SERVERÖвéѯµ½µÄÊý¾Ýµ¼³ÉÒ»¸öExcelÎļþ

T-SQL´úÂ룺

EXEC master..xp_cmdshell bcp ¿âÃû.dbo.±íÃûout c:\Temp.xls -c -q -S"servername" -U"sa" -P""

²ÎÊý£ºS ÊÇSQL·þÎñÆ÷Ãû£»UÊÇÓû§£»PÊÇÃÜÂë

˵Ã÷£º»¹¿ÉÒÔµ¼³öÎı¾ÎļþµÈ¶àÖÖ¸ñʽ

ʵÀý:EXEC master..xp_cmdshell bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"

EXEC master..xp_cmdshell bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword

ÔÚVB6ÖÐÓ¦ÓÃADOµ¼³öEXCELÎļþ´úÂ룺

Dim cn As New ADODB.Connection

cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

cn.execute "master..xp_cmdshell bcp "SELECT col1, col2 FROM ¿âÃû.dbo.±íÃû" queryout E:\DT.xls -c -Sservername -Usa -Ppassword"

¡¡¡¡4¡¢ÔÚSQL SERVERÀïÍùExcel²åÈëÊý¾Ý:

insert into OpenDataSource£¨ Microsoft.Jet.OLEDB.4.0,

Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0£©...table1 £¨A1,A2,A3£© values £¨1,2,3£©

T-SQL´úÂ룺

INSERT INTO

OPENDATASOURCE£¨Microsoft.JET.OLEDB.4.0,

Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls£©...[Filiale1$]

£¨bestand, produkt£© VALUES £¨20, Test£©

¡¡¡¡×ܽ᣺ÀûÓÃÒÔÉÏÓï¾ä£¬ÎÒÃÇ¿ÉÒÔ·½±ãµØ½«SQL SERVER¡¢ACCESSºÍEXCELµç×Ó±í¸ñÈí¼þÖеÄÊý¾Ý½øÐÐת»»£¬ÎªÎÒÃÇÌṩÁ˼«´ó·½±ã£¡

--------------------------------------------------------------------------------
Ïà¹ØÎÄÕÂ
ÈçºÎÄÜͨ¹ý´°Ìå·ÃÎÊ±í£¬µ«²»ÄÜÖ±½Ó¶ÁÈ¡±í£¿ 2004-8-27 21:58:17
Êý¾Ý¿âÉè¼ÆÖеÄÃô½Ý·½·¨ 2004-2-9 11:19:16
µ÷ÓÃoutlookÉú³É²¢·¢ËÍÓʼþµÄ´úÂë 2004-1-30 10:32:07
SQL SERVER»ù±¾¸ÅÄîѧϰӡÏó 2004-1-1 21:23:13
Ò»×éSQL ServerÉí·ÝÑéÖ¤¹ÜÀí×Ó³ÌÐò 2003-12-31 16:29:54
Access Êý¾Ý¿âºÍ Access ÏîÄ¿Ö®¼äµÄÊý¾ÝÀàÐ͵ıȽϻòÓ³Éä 2003-12-26 14:20:19
´óÐÍÊý¾Ý¿âÉè¼ÆÔ­Ôò 2003-12-15 12:49:18
ERwin--¼ò»¯µÄÊý¾Ý¿âÉè¼Æ¹¤¾ß 2003-12-15 12:41:40
ERwinÈí¼þ¼ò½é 2003-12-15 12:39:55
¾­µä×Ó²éѯӦÓà 2003-11-17 8:54:51
Á˽â Microsoft Access °²È«ÐÔ 2003-11-13 11:21:08
²»µ±±àдSQLÓï¾äµ¼ÖÂϵͳ²»°²È« 2003-11-13 10:55:26
±ÜÃâAccessºÍSQL ServerµÄ¿ÕÖµ³åÍ» 2003-10-17 20:03:19
±í´ïʽÉú³ÉÆ÷Öеļ¸ÖÖ³£Ó÷ûºÅ 2003-10-17 19:55:56
´´½¨ÍêÃÀ±¨±í 2003-10-17 19:37:17
£Ó£Ñ£Ì Óï·¨²Î¿¼ÊÖ²á 2003-10-17 19:12:40
´æ´¢¹ý³ÌÈëÃÅ 2003-10-17 19:06:48
MS Jet SQL for Access 2000Öм¶Æª(IV) 2003-10-17 18:52:20
MS Jet SQL for Access 2000Öм¶Æª (III) 2003-10-17 18:51:47
MS Jet SQL for Access 2000Öм¶Æª (II) 2003-10-17 18:51:01
MS Jet SQL for Access 2000Öм¶Æª (I) 2003-10-17 18:47:38
ʹÓÃADOÁ¬½Óµ½·À»ðǽºóµÄSQL Server 2003-10-17 18:43:57
ADOÁ¬½ÓÊý¾Ý¿â×Ö·û´®´óÈ« 2003-10-17 18:40:47
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨¶þ£© 2003-10-14 8:45:33
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Èý£© 2003-10-14 8:44:20
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Ò»£© 2003-10-14 8:43:26
ACCESSÊý¾Ý·ÃÎÊÒ³ÅäÖÃʵÀý 2003-10-14 8:36:00
ÌáÉýÇ°ºǫ́ģʽ³ÌÐòµÄËÙ¶È 2003-10-10 10:17:08
AccessÆô¶¯ÃüÁîÐÐÑ¡Ïî´óÈ« 2003-10-10 10:02:00
Ò»¸ö¼òµ¥µÄAdoÓ÷¨Ê¾Àý 2003-10-6 16:24:38
Access 2002 ÖеÄÃû³Æ×Ô¶¯¸üÕý˵Ã÷ 2003-10-6 15:16:24
Êý¾Ý¿âÓ¦ÓóÌÐòÐè×¢ÒâµÄÎÊÌâ 2003-10-6 11:11:06