SQLSERVER批量插入Bulk Insert介绍

Bulk Insert作用是将指定文件中的数据导入到特定的表或视图中。

在特定场合下,该语句发挥中很大的作用:

(1)将一个数据库表中的记录导入到另一个数据库的表中。虽然可以用SQL的导入导出,但Bulk Insert可以只针对筛选出来的结果集,更灵活。笔者有时为了调试客户问题,就常用于从客户环境中查询出相关表数据后导入到本地来调试;

(2)导入测试数据。很多情况下,都会对功能进行性能测试,需要在表中创建大量的数据,这时Bulk Insert必然是首选;

语法:

BULK INSERT   
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]   
    FROM 'data_file'   
    [ WITH   
    (   
[ [ , ] BATCHSIZE = batch_size ]   
[ [ , ] CHECK_CONSTRAINTS ]   
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]   
[ [ , ] DATAFILETYPE =   
    { 'char' | 'native'| 'widechar' | 'widenative' } ]   
[ [ , ] DATASOURCE = 'data_source_name' ]
[ [ , ] ERRORFILE = 'file_name' ]
[ [ , ] ERRORFILE_DATA_SOURCE = 'data_source_name' ]   
[ [ , ] FIRSTROW = first_row ]   
[ [ , ] FIRE_TRIGGERS ]   
[ [ , ] FORMATFILE_DATASOURCE = 'data_source_name' ]
[ [ , ] KEEPIDENTITY ]   
[ [ , ] KEEPNULLS ]   
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]   
[ [ , ] LASTROW = last_row ]   
[ [ , ] MAXERRORS = max_errors ]   
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]   
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]   
[ [ , ] ROWTERMINATOR = 'row_terminator' ]   
[ [ , ] TABLOCK ]   

-- input file format options
[ [ , ] FORMAT = 'CSV' ]
[ [ , ] FIELDQUOTE = 'quote_characters']
[ [ , ] FORMATFILE = 'format_file_path' ]   
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]   
[ [ , ] ROWTERMINATOR = 'row_terminator' ]   
    )]

具体参数说明可文末给出的参考链接,这里直接上示例。

准备

建立表

首先在数据库中建立表userinfo:

create table userinfo(
    id int,
    name varchar(36)
);

为了简单,这里不添加任何约束了,后续再说明。

准备数据文件

这里使用csv文件格式,用EXCEL打开可以对其进行编辑,里面只有两列,分别表示id、name.

id,name
1,god
1,god
1,god
1,god
1,god
1,god

这里直接,使用相同数据行,在实际应用中,如果需要校验导入数据的完整性,可以添加CHECK_CONSTRAINTS选项,该选项表示导入时会校验数据库的约束条件。默认是不校验的,估计是问了提高性能吧。官方文档中对此有描述,表示可以先不用约束导入,然后使用SQL语句对导入的数据进行合法性检查及处理。

导入

执行导入操作:

bulk insert test.dbo.userinfo
from 'f:\data.csv'
with
(
    datafiletype = 'char',
    
    firstrow = 2,
    fieldterminator=','
)

简单解释下几个参数:

datafiletype:  源文件的编码格式,默认是char,也就是ASCII;
firstrow:      从源文件的第几行开始读取,这里data.csv中第一行是                 列名,所以应为文件的第二行开始。另外注意下标是从1开始的。
fieldterminator:字段分隔符,data.csv中使用","分割,所以这里使用“,”即可。
rowterminator:  行分隔符,默认为换行符。

test.dbo.userinfo的格式是数据库名.模式名.表名。

执行后,再次查询结果,可以看到已经有99条记录。

既然Bulk Insert是为了批量而生,那我们来看看批量执行的效率吧。

  • 导入10W条记录:<1s;
  • 导入100W条记录:4s;

这里有必要说明下,主要是因为userinfo表的字段、数据都很简单,所以很快。实际上当表的字段更多,值内容更丰富时,结果会慢一些,但也远比使用程序进行插入快速很多。

参考:

更多内容,请参考MSDN说明:Bulk Insert.

标签: SQL, SQLSERVER, Bulk Insert, 批量插入

添加新评论