批量导入（bulk insert）csv文件到SQL server

roych

前不久在某个论坛（ExcelHome）上有人问，能否批量导入数据到SQL server？虽然之前也有数据同步的情况，但需要导入文件的情况极少，所以也就手动按向导完成罢了。
这么一问，倒是把我问到了。终于今天得闲，就抽空去微软官网看看。导入大概有三种方式。一种是bcp，一种是bulk insert，另一种则是openrowset。

bcp只能对最简单的格式进行导入，不能指定表头（或许我没找到这方面的例子吧）。而openrowset则通常需要格式化文件（fmt）或者指定数据库链接引擎（例如，access）等参数，对于文本文件，bulk insert似乎更简单一些。所以这里只谈谈bulk insert。
按惯例，先上代码。

1. CREATE PROC [dbo].[import_data] @start_date date='2020-07-01', @end_date date='2020-07-31'
   
2. AS
   
3. BEGIN
   
4.         DECLARE @file_name varchar(100)--文件名
   
5.         DECLARE @day_num int--天数
   
6.         DECLARE @date date--日期变量
   
7.         DECLARE @i int--序号变量
   
8.         DECLARE @tmp_table table(is_file_exists int,is_path int,is_parent_exists int)--文件信息变量表，字段1：是否存在文件。字段2：是否文件夹，字段3：父级路径是否存在
   
9.         DECLARE @str_sql varchar(500)--SQL语句拼接
   
10.         --初始化起止日期天数，用于确定循环上限
    
11.         SET @day_num=DATEDIFF(day,@start_date,@end_date)
    
12.         --初始化日期变量。
    
13.         SET @date=@start_date
    
14.         SET @i=0
    
15. 
    
16.         WHILE @i<=@day_num
    
17. 
    
18.         BEGIN
    
19.                 SET @file_name='E:\AppData\Local\'+convert(varchar(8),@date,112)+'.csv'
    
20.                 INSERT INTO @tmp_table EXEC [MASTER]..XP_FILEEXIST @file_name --把文件信息插入文件信息变量表
    
21. 
    
22.                 IF EXISTS(SELECT * FROM @tmp_table WHERE is_file_exists=1) --如果文件存在，则拼接SQL语句
    
23.                         SET @str_sql= 'BULK INSERT v_test FROM '''+@file_name
    
24.                                                         +''' WITH (FIRSTROW=2,FIELDTERMINATOR='','')'
    
25.                 ELSE --否则就跳出循环
    
26.                         BREAK
    
27.                
    
28.                 delete from test where import_date=@date --如果已经导入，则删除数据
    
29.                 EXEC(@str_sql) --导入数据
    
30.                 update test set import_date=@date where import_date is null --添加导入日期
    
31.                 DELETE FROM @tmp_table --删除文件信息变量表。注意，这里可不能truncate。
    
32.                 SET @i=@i+1 --循环累计
    
33.                 SET @date=DATEADD(day,@i,@start_date)  --重设日期变量
    
34.         END
    
35. 
    
36. END

复制代码

先说下基本格式：
BULK INSERT 表名（或视图名）'C:\测试.csv' WITH (FIRSTROW=2,FIELDTERMINATOR=',')
说明：
FIRSTROW=2：可选（默认情况下从第一行导入）。这里表示从第二行导入。
FIELDTERMINATOR=','：必选，表示csv分割符为逗号。SQL server 2017版本【即版本号为14以上】以后可以用FORMAT='CSV'代替这一句。

版本号查看请参考下图：

附件如下：

roych

其实存储过程里的注释已经写得很清楚了。不过我还是想讲下思路，便于大家理解。
在讲解之前，我们先做这么一个假设。
假定这批CSV文件都是以日期命名的，当然，你也可以加上前缀，例如，“被割韭菜20200701.csv”，不过，不建议具体到时分秒，更不建议加后缀名，这样处理难度比较大。
接下来我们大体画个流程图（看不清流程图的话，请自行下载附件）：
大体需要做3个判断。

• 判断日期是否达到。这个作为条件来终止循环。
• 判断是否存在文件。这个用来防止导入出错。
• 判断是否已经导入。这个用来防止重复导入。
  

解决方案：

• 我们需要定义一个日期变量（@date），它将从开始日期，每次循环累加一天，直至达到终止日期。由此，需要衍生出一个计数变量（@i）。是否设置变量来计算起止时期差呢？见仁见智。我这里是设置了的。如果不设置，也可以直接用datediff函数处理。
• 这道题超纲了哈。命令EXEC [MASTER]..XP_FILEEXIST "文件路径"，将返回是否存在某文件的信息。为了便于处理，我们需要创建一个表变量来存储它。因此需要定义一个文件路径的字符串变量（@file_name），以及一个表变量（@tmp_table）。
• 如何判断重复导入？自然是在导入时添加一个日期了，因此，除了数据源所需字段之外，目标表还需要建立导入日期字段（import_date）。这样的话，流程上先删除当日已导入的数据，然后插入当日数据，再添加当日日期。就不至于多次导入重复数据了。
• 由于导入的文件名是变量，我们无法直接执行bulk insert，需要拼接好SQL语句再执行，因此这里需要一个拼接SQL的字符串变量（@str_sql）
  

综上，我们需要定义的变量有：

• @date（日期变量）、@i（计数变量）、@day_num（起止时间段天数）
• @file_name（文件路径变量）、@tmp_table（文件信息表变量）
• @str_sql（SQL语句拼接变量）
• 需要添加一个字段（import_date）
  

添加完毕后，就是写存储过程了。大家可以看上面的帖子，注释应该算比较详尽的，就不一一解析了。
如果还有什么问题，也可以在后面的帖子上评论或回复。

admin

赞一个

Excel3000

牛，感谢分享，大数据导入很实用，节省不少时间

worryd1

这个应该非常有用，先收藏一下