澳门二十一点游戏赌场 澳门二十一点游戏赌场
    您现在的位置:首页 >> 软件应用 >> SQL教程 >> 内容

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

    时间:2012/7/23 20:06:30 点击:

      核心提示:使用T-SQL导入多个文件数据到SQL Server中 在我们的工作中,经常需要连续输入多个文件的数据到SQL Server的表中,有时需要从相同或者不同的目录中,同时将文件中的数据倒入。在这篇文章中...

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

    Tags:使用 用T TS SQ 
    作者:佚名 来源:不详

    共有评论 0相关评论
    发表我的评论
    • 大名:
    • 内容:
  • 澳门二十一点游戏赌场(www.0769online.com) © 2019 版权所有 All Rights Reserved. 邮箱:103150@qq.com 业务QQ:103150 手机:13549753683

    Power By Laoy8 SQL 3.0SP1 粤ICP备10098668号
  •