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裡查詢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試算表軟體中的資料進行轉換，為我們提供了極大方便！