数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
使用T-SQL导入多个文件数据到SQL Server中

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

使用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中




相关文章

相关软件