SQL批量导入

来源：互联网发布：网络收纳管理咨询机构编辑：程序博客网时间：2024/06/06 19:50

有一文件[D:/msql/bulk.txt]内容如下.以空格分隔.
                    01   gwf   dongguang   CSDN.net
                    02   sxl   dongguangeric   CSDN.net
  --建表:
  if   object_id('tbulk')   is   not   null   drop   table   tbulk
  create   table   tbulk(id   int,name   varchar(100),addr   varchar(100),tag   varchar(10))
  Go
  --批次处理
  bulk   insert   tbulk   from   'D:/msql/bulk.txt'
  with   (
  fieldterminator='   '   --列分隔符
  -- rowterminator='/n'                   --行分隔符
            )
  --结果
  select   *   from   tbulk

不要使用INSERT导入大批的数据

  　　请不要这样做，除非那是必须的。使用UTS或者BCP，这样你可以一举而兼得灵活性和速度。
  使用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命令来重命名或者移到不同的目录中。