access sql server 資料庫 資料匯出

昨天弄了一個比較棘手的問題。從網上下載了一個軟體，他的資料庫是access的，開啟看了一下，感覺不錯，適合我現在項目的需求，大部分能夠滿足我的項目需要，就想拿來主義。可是我們項目的資料庫一直都是用的sqlserver，於是，就在網上瘋狂的，找關於access轉換為sqlserver的資料在這裡我想說一下有關的注意事項：

資料庫升遷轉換access---sqlserver：

   1.首先要說的是你的資料庫必須是，通過安裝嚮導安裝的，綠色版的access不行。 

   2 .如果被轉換的資料庫版本比較早，例如是access97，需要先將資料庫用access轉換問2000或者2003。

   3.轉換的過程步驟就不說了，網路上很多。然後開啟轉換後的access資料庫，執行“工具”---“資料庫工具 + 生產力”---“升遷嚮導”，將access資料庫轉換為sqlserver資料庫，轉換完成後，開啟sqlserver資料庫的“企業管理器”就會探索資料庫已經在裡面了。

 

資料庫資料匯出問題：

   今天費了很長的時間完成這個難題。還是幼稚的在Google上面搜“sql 資料匯出 ”之類的。

    方法一：利用資料庫匯出嚮導sqlserver來完成匯出

               開啟sqlserver企業管理器“工具”---“資料轉換服務”----“匯出資料”-------點“下一步”---在“選擇資料來源”對話方塊內選擇資料來源和伺服器，在下面的“資料庫”選擇要匯出的資料庫。點“下一步”---在“選擇目的”對話方塊內的“目的”選擇匯出到“資料庫”還是到“文字檔”或者其他的選項。如果選擇“文字檔”在填寫檔案名稱和路徑。在點“下一步”---“下一步”選擇“源”（即要匯出的表名稱”選擇檔案類型，行/資料行分隔符號。等。點下一步。。選擇”立即運行“選擇下一步。。。就完成了。

    方法二：利用“預存程序”。上面的方法不能滿足我的要求，我想一次全部將所有的表內的資料全部匯出，上面的方法不行。所以我就從網上搜關於預存程序的例子。但是小弟我笨，最後還是沒有弄出來，但是，還是弄這個預存程序，也學到了不少的東西，所以就拿出來，分享，供新手學習。

     從網上搜來的預存程序編寫：

     

 

/*--資料匯出EXCEL
 
 匯出查詢中的資料到Excel,包含欄位名,檔案為真正的Excel檔案
 如果檔案不存在,將自動建立檔案
 如果表不存在,將自動建立表
 基於通用性考慮,僅支援匯出標準資料類型

--鄒建 2003.10(引用請保留此資訊)--
增加分頁功能
6.5w條一頁
--Add by 謝小漫--
*/

-----------------------------預存程序編寫begin-----------------------
CREATE         proc p_exporttb
@sqlstr varchar(8000),   --查詢語句,如果查詢語句中使用了order by ,請加上top 100 percent
@path nvarchar(1000),   --檔案存放目錄
@fname nvarchar(250),   --檔案名稱
@sheetname varchar(250)=''  --要建立的工作表名,預設為檔案名稱
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000),@tmpsql varchar(8000)
declare @sheetcount int,@sheetnow int, @recordcount int, @recordnow int
declare @sheetsql varchar(8000)--建立頁的sql

declare @pagesize int
set @pagesize = 65000--sheet分頁的大小
--set @pagesize = 1000

--參數檢測
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--檢查檔案是否已經存在
if right(@path,1)<>'/' set @path=@path+'/'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--資料庫建立語句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'

--串連資料庫
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--建立表的SQL
declare @tbname sysname

set @tbname='##tmp_'+convert(varchar(38),newid())

declare @tbtmpid nvarchar(50)
set @tbtmpid ='tmp_'+convert(varchar(38),newid())+''

--有序列ID @tbtmpid的暫存資料表
set @sql='select Identity(int,1,1) as ['+@tbtmpid+'], a.* into ['+@tbname+'] from ( select top 100 percent b.* from ( '+@sqlstr+') b) a'
exec(@sql)

--print(@sql)

--取得記錄總數
set @recordcount= @@rowcount
if @recordcount=0 return
--print @recordcount

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case
   when b.name like '%char'
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)
and a.name <> @tbtmpid

set @fdlist=substring(@fdlist,2,8000)
--print @fdlist

--列數為零
if @@rowcount=0 return

set @sheetsql = @sql

--print @sheetsql

--匯入資料
--頁數
set @sheetcount = CEILING(@recordcount/CAST(@pagesize as float))
--print @sheetcount
--只是一個頁而已
IF @sheetcount = 1 BEGIN
    --print '只是一個頁而已'

    set @sql='create table ['+@sheetname
     +']('+substring(@sheetsql,2,8000)+')'
    
   
    exec @err=sp_oamethod @obj,'execute',@out out,@sql
    if @err<>0 goto lberr
   

 

    set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
       ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
   
    exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
END

--多個頁

set @sheetnow = @sheetcount
set @recordnow= 0
IF @sheetcount > 1 BEGIN
    --print '多個頁'
WHILE @sheetnow > 0 BEGIN

    --建立頁
    set @sql='create table ['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1)
    +']('+substring(@sheetsql,2,8000)+')'
    
   
    exec @err=sp_oamethod @obj,'execute',@out out,@sql
    if @err<>0 goto lberr
   
    --print @sql
    --建立頁end

    IF @sheetnow = @sheetcount BEGIN
        set @tmpsql ='select top '+str(@pagesize)+' '+@fdlist+' from ['+@tbname+']'
        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'
       
        exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql)
    END
    IF @sheetnow < @sheetcount BEGIN   
        set @tmpsql='select top '+str(@pagesize)+' '+@fdlist+' from ['+@tbname+'] where ['+@tbtmpid
    +'] not in ( select top '+str(@recordnow-@pagesize)+' ['+@tbtmpid+'] from ['+@tbname+'])'

        set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
    ;DATABASE='+@path+@fname+''',['+@sheetname+'_'+ convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'
       
        exec('insert into '+@sql+'('+@fdlist+') '+ @tmpsql)
        --print (@tmpsql)   
        --exec(@tmpsql)
    END
   
    --print (@tmpsql)   

    --exec (@tmpsql)   

    set @recordnow = @pagesize*(@sheetcount-@sheetnow+2)
    set @sheetnow = @sheetnow -1
END
END

set @sql='drop table ['+@tbname+']'
exec(@sql)

exec @err=sp_oadestroy @obj

--結束返回
return

lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 錯誤號碼
  ,@src as 錯誤源,@desc as 錯誤描述
 select @sql,@constr,@fdlist

SET QUOTED_IDENTIFIER OFF

GO
--------------------------------預存程序編寫end-----------------

 

上面的代碼只要拿到“查詢分析器”內執行以下就oK了。這個預存程序就存到系統中了。

 

調用預存程序：

     exec 預存程序名稱  查詢語句,儲存位置,  保持名稱

   執行個體：

     exec p_exporttb 'select * from department','c:/',  'department'

----------------------------------------

上面的可以完成表的匯出，但是還是不能將表全部匯出。我研究了半天也沒有弄出來還請讀者不過可以提供給你們下面研究的東西，下面能夠實現迴圈能夠得到資料庫中的表名稱。

   declare @path varchar(100)
declare @filename varchar(100)
declare @sql varchar(100)
declare @esql varchar(100)
set @sql = 'select * from '
declare @pre varchar(100)
set @pre = 'p_exporttb '
set @path = ''
DECLARE   abc   CURSOR   FOR    
  SELECT   [name]   from   sysobjects   where   xtype='u'
  OPEN   abc    
  FETCH   NEXT   FROM   abc
  fetch   abc into @filename
  while   @@FETCH_STATUS   =   0  
  begin  

--set @esql = @pre+' '+@sql+@filename+' , '+@path+' , '+@filename
 --  exec(@esql)

             print @filename
  FETCH   NEXT   FROM   abc  
  fetch   abc into @filename
  end
  CLOSE   abc  
  DEALLOCATE   abc

 

方法三：利用外界工具。在中午吃飯 的時間跟同事討論這個問題。他們說有專門的工具啊。於是從網上搜了一個叫“sqltotext”的軟體。的確好用，功能夠我自己用的。大家可以搜尋一下。。介面英文版的，不難，應該能看懂。。