使用T-SQL匯入多個檔案資料到SQL Server中

在我們的工作中，經常需要連續輸入多個檔案的資料到SQL Server的表中，有時需要從相同或者不同的目錄中，同時將檔案中的資料倒入。在這篇文章中，我們將討論如何同時把一個目錄中的檔案的資料倒入到SQL Server中。 實驗環境
    我們先建立整個實驗的環境。建立檔案目錄“C:\MyImport”，和三個檔案a.csv、b.csv和c.csv，檔案內容如下。同時，在SQL Server中建立一個表用來存放匯入的資料。
        C:\MyImport\a.csv
        1, MAK, A9411792711, 3400.25
        2, Claire, A9411452711, 24000.33
        3, Sam, A5611792711, 1200.34
        C:\MyImport\b.csv
        11, Rubon, 9671792711, 400.14
        22, Mike, 9418952711, 4000.56
        39, Hsu, 75611792511, 1230.00
        C:\MyImport\c.csv
        69, Lucy, 8411992710, 305.11
        45, Grace, 3413452713, 246.52
        33, Saint, 5461795716, 1278.70
        Create Database Bank
        Go
        Use Bank
        go
        Create table Account
        (
            [ID] int, Name Varchar(100),
            AccountNo varchar(100), Balance money
        )
        Go
        Create table logtable
        (
            id int identity(1,1),
            Query varchar(1000),
            Importeddate datetime default getdate()
        )       
    方法 1: XP_CMDSHELL 和BULK INSERT
    這個方法使用xp_cmdshell和Bulk Insert的SQL命令把一個目錄中的檔案倒入到SQL Server的表中。

建立預存程序

    在資料庫中產生這個預存程序，這個預存程序有三個參數：檔案路徑，副檔名和資料庫的表名。

Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

執行
執行上面的預存程序，參數如下：
例1：輸入所有的c:\myimport目錄下的.csv檔案到Account表中
Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

例2：輸入所有的c:\myimport目錄下的檔案到Account表中
Exec usp_ImportMultipleFiles 'c:\myimport\', '*.*', 'Account'

    方法 2: XP_CMDSHELL 和 BCP 公用程式
    這個方法使用 "xp_cmdshell" 和 "BCP.exe" 倒入一個目錄下的檔案到SQL Server的表中。這個預存程序以伺服器名、資料庫名、檔案路徑、副檔名和資料庫表名作為參數。
    注意：確信你運行SQL Server Agent的帳戶具有訪問你輸入的檔案夾和伺服器的許可權。

建立預存程序：
set quoted_identifier off
go
Create procedure usp_ImportMultipleFilesBCP @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
--select * from #y
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r\n -t,'
set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+  @query +"'"
--print @query
EXEC ( @query)
insert into logtable (query) select @query
end

drop table #y

建立批次檔
 　如下所示建立批次檔C:\MyImport\Import.bat。
　　　　REM Type: Batch File
　　　　REM Created by: Digjim
　　　　REM Import all csv files to SQL Server using DTS
 
　　　　REM Export DIR listing to C:\MyImport\Dirlist.txt
　　　　dir c:\MyImport\*.csv /b > C:\MyImport\Dirlist.txt
 
　　　　REM Execute DTS package for every file name in the Dirlist.txt

　 　　　for /f "tokens=1,2,3" %%i in (C:\MyImport\Dirlist.txt) do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" -F "c:\myimport\myimportdts1.dts" -U importuser -P import –A "ServerName"="DIGJIM" -A "FileName"="c:\myimport\%%i" -A "DatabaseName"="Bank"

　　　　REM Rename all the files with "old" as suffix
　　　　ren c:\myimport\*.csv *.oldcsv

　　執行批次檔
  　　當批次檔被執行以後，他會在C:\MyImport目錄下產生一個DirList.txt的檔案，這個檔案會包含所有C:\MyImport下擴 展名為CSV的檔案，這些檔案名稱會和其他必須的參數一氣傳輸給DTSrun.exe。在這個例子裡，DirList.txt的內容如下：
　　　　C:\MyImport\DirList.txt
　　　　acsv
　　　　b.csv
　　　　c.csv
 　　注意，在批次檔中，根據你自己的情況設定ServerName，FileName和DatabaseName參數。

執行
    執行上面的預存程序，參數如下：
例1：輸入所有的c:\myimport目錄下的.csv檔案到Account表中
Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.csv','Account'

例2：輸入所有的c:\myimport目錄下的檔案到Account表中
Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.*','Account'

    結果
    不管你使用方法一還是方法二，資料將會被匯入到SQL Server的表中。如下所示：

1
 MAK
 A9411792711
 3400.25
 
2
 Claire
 A9411452711
 24000.33
 
3
 Sam
 A5611792711
 1200.34
 
11
 Rubon
 9671792711
 400.14
 
22
 Mike
 9418952711
 4000.56
 
39
 Hsu
 75611792511
 1230
 
69
 Lucy
 8411992710
 305.11
 
45
 Grace
 3413452713
 246.52
 
33
 Saint
 5461795716
 1278.7
 

使用方法一產生的日誌記錄如下表：

 

1
 BULK INSERT Account FROM "c:\myimport\a.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n") 
 3/1/04 2:15
 
2
 BULK INSERT Account FROM "c:\myimport\b.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")
 3/1/04 2:15
 
3
 BULK INSERT Account FROM "c:\myimport\c.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")
 3/1/04 2:15
 

使用方法二產生的日誌記錄如下表：

1
 MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\a.csv" -SSQL -T -c -r\n -t,'
 3/1/04 2:18
 
2
 MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\b.csv" -SSQL -T -c -r\n -t,'
 3/1/04 2:18
 
3
 MASTER.DBO.xp_cmdshell 'bcp "Bank.dbo.Account" in "c:\Myimport\c.csv" -SSQL -T -c -r\n -t,'
 3/1/04 2:18
 
 
總結
    像前面提到的一樣，這篇文章的目的是告訴你如何從一個檔案夾中匯入多個檔案到SQL Server的表中。這些預存程序可以進一步增強，可以用xp_getfiledetails來判斷轉入的檔案的最後修改時間來確定檔案的範圍。這些儲存 過程也可以建立為定時執行的作業。在匯入完成以後，你可以用rename或move命令來重新命名或者移到不同的目錄中。

如果你把DTS包儲存在SQL Server中，批次檔就這樣寫：
     REM Type: Batch File
     REM Created by: Digjim
     REM Import all csv files to SQL Server using DTS
 
     REM Export DIR listing to C:\MyImport\Dirlist.txt
     dir c:\MyImport\*.csv /b > C:\MyImport\Dirlist.txt
 
     REM Execute DTS package for every file name in the Dirlist.txt

     for /f "tokens=1,2,3" %%i in (C:\MyImport\Dirlist.txt) do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" –S "SQL" –N "myimportdts" -U importuser -P import –A "ServerName"="DIGJIM" -A "FileName"="c:\myimport\%%i" -A "DatabaseName"="Bank"

     REM Rename all the files with "old" as suffix
     ren c:\myimport\*.csv *.oldcsv

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/zhou__zhou/archive/2007/08/11/1737931.aspx