SQL SERVER 與ACCESS、EXCEL的資料轉換 @ SE's PC 個人工作室 :: 隨意窩 Xuite日誌
  • BloggerAds
  • 購物中心
  • 消費電子-娛樂新機通通有
  • 關鍵字
  • BloggerAds
  • 特力屋
  • 甚麼都買
    1. 沒有新回應!
  • 200610241054SQL SERVER 與ACCESS、EXCEL的資料轉換
    平均分數:0 顆星    投票人數:0
    我要評分:
    熟悉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: rade.mdb'; 'admin'; ', 資料表名稱)
    SELECT *
    FROM sqltablename
    -------------------------------------------------------------------------------------------------
    二、 SQL SERVER 和EXCEL的資料匯入匯出
    1、在SQL SERVER裡查詢Excel資料:
    -- ======================================================
    SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c: ook1.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:Financeaccount.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: ook1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
    實例:
    SELECT * into newtable
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:Financeaccount.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: emp1.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: raininginventur.xls')...[Filiale1$]
    (bestand, produkt) VALUES (20, 'Test')
    -------------------------------------------------------------------------------------------------
    總結:利用以上語法,我們可以方便地將SQL SERVER、ACCESS和EXCEL軟體中的資料進行轉換,為我們提供了非常大方便!
    參考:
    http://www.itrain.de/knowhow/sql/transfer/adhoc/
    回應