使用T-SQL导入多个文件数据到SQL Server中

在我们的工作中,经常需要连续输入多个文件的数据到SQL Server的表中,有时需要从相同或者不同的目录中,同时将文件中的数据倒入。在这篇文章中,我们将讨论如何同时把一个目录中的文件的数据倒入到SQL Server中。  [@more@]   试验环境
    我们先创建整个试验的环境。创建文件目录“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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值