〖学习〗【翻译】同时倒入多个文件数据到SQL Server中,感谢大家

DigJim 2004-05-03 10:05:04
感谢大家祝福我那可爱的儿子!
与儿子作战的同时,忙里偷闲,翻译一点简单的文章,大家一起学习交流!

---------------------------------------------------------

使用DTS导入多个文件数据到SQL Server中
http://www.csdn.net/Develop/read_article.asp?id=27081

---------------------------------------------------------

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

...全文
33 10 点赞 打赏 收藏 举报
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
JerryOne 2004-05-11
CSDN好同志真多!
  • 打赏
  • 举报
回复
netcoder 2004-05-11
好同志,顶上去
  • 打赏
  • 举报
回复
internetcsdn 2004-05-08
CSDN上多少当爹娘的了?
  • 打赏
  • 举报
回复
fornothing 2004-05-08
呵呵 学习~~~~~` 忙里偷闲阿

欢迎再偷闲
  • 打赏
  • 举报
回复
zjcxc 元老 2004-05-03
--再改改第二个

Create procedure usp_ImportMultipleFilesBCP
@servername varchar(128),
@DatabaseName varchar(128),
@filepath varchar(500),
@pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(8000)

create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)

declare tb cursor local for
select sql='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+name+'" -S' + @servername + ' -T -c -r\n -t,'
from #x where name is not null

open tb
fetch next from tb into @query
while @@fetch_status=0
begin
exec master..xp_cmdshell @query
insert into logtable (query) select @query
fetch next from tb into @query
end
close tb
deallocate tb
  • 打赏
  • 举报
回复
leeboyan 2004-05-03
楼上的三个当爹的抢了我的1、2、3楼
  • 打赏
  • 举报
回复
WhbtomT 2004-05-03
带小孩,辛苦!
但是乐在其中。
  • 打赏
  • 举报
回复
zjcxc 元老 2004-05-03
--没事,改改上面的存储过程

--第一个这样改一下,我觉得容易看一点:

Create procedure usp_ImportMultipleFiles
@filepath varchar(500),
@pattern varchar(100),
@TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(8000)

create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec(@query)

declare tb cursor local for
select sql='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+name+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
from #x where name is not null

open tb
fetch next from tb into @query
while @@fetch_status=0
begin
exec (@query)
insert into logtable (query) select @query
fetch next from tb into @query
end
close tb
deallocate tb
  • 打赏
  • 举报
回复
DigJim 2004-05-03
老婆带着,我偷跑开一会儿!
  • 打赏
  • 举报
回复
zjcxc 元老 2004-05-03
DigJim (挖土) 不用带小DigJim (挖土) ?
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-05-03 10:05
社区公告
暂无公告