分享
 
 
 

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

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

使用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中[url=http://www.csdn.net/Develop/read_article.asp?id=27457][/url]

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有