作者：未知 来源：月光软件站 加入时间：2005-2-28　月光软件站

使用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 '[email protected][email protected] +' /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 @[email protected]+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @[email protected]+'"
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 '[email protected][email protected] +' /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 @[email protected]+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'[email protected] + '"
in "'+ @[email protected]+'" -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

执行
    执行上面的存储过程，参数如下：
例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

使用方法一产生的日志记录如下表：

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

相关软件：