使用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
使用方法一產生的日志記錄如下表：
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中
