使用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命令来重命名或者移到不同的目录中。
参考:

|