sql sever中批量导入大量的数据

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