IDC资讯中心
使用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
执行
执行上面的存储过程，参数如下：
例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中
