Sql server 中的bulk insert语句使用

在SQL Server中，BULK INSERT语句用于将数据从文本文件或CSV文件中导入到数据库表中，它是一个非常强大的工具，可以处理大量的数据插入操作，本文将详细介绍如何使用BULK INSERT语句，以及一些常见的使用技巧和注意事项。

BULK INSERT语句的基本语法

BULK INSERT语句的基本语法如下：

BULK INSERT table_name
FROM 'data_file_path'
WITH (
    FIELDTERMINATOR = 'field_terminator', --字段分隔符，默认为逗号
    ROWTERMINATOR = 'row_terminator', --行分隔符，默认为新行字符
    FIRSTROW = first_row, --起始行号，默认为1
    LASTROW = last_row, --结束行号，默认为-1（表示所有行）
    BATCHSIZE = batch_size, --每次插入的行数，默认为1000
    ERRORFILE = 'error_file_path', --错误日志文件路径，默认为空
    TABLOCK, --提示服务器锁住要插入的表
    KILOBYTES_PER_BATCH = kilobytes_per_batch, --每批次的字节数，默认为2147483647（2GB）
    CHECK_CONSTRAINTS, --检查约束
    CODEPAGE = codepage, --代码页，默认为不指定
    REMOVE_ROWS, --删除已插入的行
    SKIP_NONE, --跳过没有数据的行
    REJECT_TABLE_HINTS, --拒绝表提示
    LOGFILE = 'log_file_path', --日志文件路径，默认为空
    STATISTICS_NORECOMPUTE, --不重新计算统计信息
    BATCH_MODE = 'load_mode' --加载模式，默认为'false'（批量模式），'true'（顺序模式）
)

BULK INSERT语句的使用示例

1、从CSV文件中导入数据到表中：

BULK INSERT sales.orders
FROM 'C:ordersorders.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '
',
    FIRSTROW = 2,
    LASTROW = 5000000,
    BATCHSIZE = 10000,
    ERRORFILE = 'C:orderserrors.log',
    TABLOCK,
    CODEPAGE = 'ACP',
    REMOVE_ROWS,
    SKIP_NONE,
    REJECT_TABLE_HINTS,
    LOGFILE = 'C:ordersbulkinsert.log',
    STATISTICS_NORECOMPUTE,
    BATCH_MODE = 'true'
)

2、从文本文件中导入数据到表中：

BULK INSERT sales.products
FROM 'C:productsproducts.txt'
WITH (
    FIELDTERMINATOR = 't', --字段分隔符为制表符（Tab）
    ROWTERMINATOR = '
', --行分隔符为换行符（New Line）
    FIRSTROW = 1, --从第一行开始导入数据
    LASTROW = 1000000, --导入100万行数据
    BATCHSIZE = 5000, --每次插入5000行数据
    ERRORFILE = 'C:productserrors.log', --错误日志文件路径
    TABLOCK, --提示服务器锁住要插入的表
    CODEPAGE = 'ACP', --代码页为系统默认值（不指定）
    REMOVE_ROWS, --删除已插入的行（不保留源文件中的数据）
    SKIP_NONE, --跳过没有数据的行（不跳过任何行）
    REJECT_TABLE_HINTS, --拒绝表提示（不使用表提示）
    LOGFILE = 'C:productsbulkinsert.log', --日志文件路径
    STATISTICS_NORECOMPUTE, --不重新计算统计信息（不更新统计信息）
    BATCH_MODE = 'false' --批量模式（一次插入多行数据）
)

BULK INSERT语句的注意事项

1、确保源文件的格式与目标表的结构相匹配，否则可能导致插入失败或数据丢失。

2、如果源文件中存在重复的行，可以使用CHECKSUM选项来检测并忽略重复行。CHECKSUM=checksum_column。

3、如果源文件中存在列名与目标表的列名相同的情况，可以使用FORMATFILE选项来指定一个格式文件，以便正确解析源文件中的列名。FORMATFILE='C:formatfile.fmt'，格式文件的内容应包含源文件中每个列的名称和类型。1 ID int，数字表示列的顺序，字符串表示列名，类型表示列的数据类型，格式文件的详细用法请参考官方文档。