SQLServer中采用bulk insert语句一次性插入十万级以上大量数据到指定的数据库表（字段数量不同）

admin

2023年7月31日 21:54 本文热度 1117

Sql server 的 bulk insert 语句可以高效的导入大数据量的平面文件（txt，csv文件）到数据库的一张表中，其用法如下：

bulk insert tableName
from 'E:\\ClickSun\\ChinaIP.csv'
with
(fieldterminator=',',
rowterminator='\n')

其中"tableName"是数据库表的名字，"E:\ClickSun\ChinaIP.csv"是导入平面文件的地址，fieldterminator 指定平面文件中列的分隔符是什么，rowterminator 指定平面文件中行的结束符是什么。

还可以使用 FIRSTROW 和 LASTROW 限制行数。如下COPY前三行：

bulk insert tableName
from 'E:\\ClickSun\\ChinaIP.csv'
with
(fieldterminator=',',
rowterminator='\n',
FIRSTROW =1,
LASTROW=3)

要把平面文件数据导入到数据库表中，平面文件只有9个字段，数据库表有12个字段，字段数量不一致，怎么把平面文件字段的对应到表的字段，如何用 bulk insert 来实现？

数据库表 china_ip_range

create TABLE china_ip_range
(
    id INT identity,
    address varchar(25),
    IPS0 tinyint,
    IPS1 tinyint,
    IPS2 tinyint,
    IPS3 tinyint,
    IPE0 tinyint,
    IPE1 tinyint,
    IPE2 tinyint,
    IPE3 tinyint,
    input_ime datetime
)

平面文件数据是：E:\ClickSun\ChinaIP.csv

address,IPS0,IPS1,IPS2,IPS3,IPE0,IPE1,IPE2,IPE3
中国北京,1,2,2,0,1,2,2,255
中国北京,1,2,4,0,1,2,4,255
中国北京,1,2,5,0,1,2,5,255
中国北京,1,2,8,0,1,2,8,255
中国北京,1,4,4,0,1,4,4,255
中国北京,1,8,0,0,1,8,255,255
中国北京,1,12,0,0,1,12,255,255
中国北京,1,13,96,0,1,13,127,255
中国北京,1,14,128,0,1,14,191,255
中国北京,1,14,224,0,1,15,255,255
中国北京,1,45,0,0,1,45,255,255
中国北京,1,88,0,0,1,91,255,255
中国北京,1,92,0,0,1,95,255,255
中国北京,1,116,0,0,1,117,255,255
中国北京,1,119,0,0,1,119,255,255
中国北京,1,202,0,0,1,203,255,255
中国北京,8,130,0,0,8,130,55,255
中国北京,8,130,56,0,8,131,255,255
中国北京,11,164,228,0,11,164,233,255

先在E盘clicksun目录下面存放一个格式化xml文件：E:\ClickSun\ChinaIP.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="3" COLLATION="Chinese_PRC_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="address" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="IPS0" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="3" NAME="IPS1" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="4" NAME="IPS2" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="5" NAME="IPS3" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="6" NAME="IPE0" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="7" NAME="IPE1" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="8" NAME="IPE2" xsi:type="SQLTINYINT"/>
  <COLUMN SOURCE="9" NAME="IPE3" xsi:type="SQLTINYINT"/>
 </ROW>
</BCPFORMAT>

然后使用 bulk insert 语句时，采用 FORMATFILE 参数指定该 ChinaIP.xml 文件：

BULK insert china_ip_range
    from 'E:\\ClickSun\\ChinaIP.csv'
WITH
(
    FORMATFILE = 'E:\\ClickSun\\ChinaIP.xml',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='\n',
    FIRSTROW = 2
)
完整写法：
set conn=opendb("oabusy","conn","accessdsn")
conn.execute("BULK insert china_ip_range from 'E:\\ClickSun\\ChinaIP.csv' WITH (FORMATFILE = 'E:\\ClickSun\\ChinaIP.xml',FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FIRSTROW = 2)")
conn.close
set conn=nothing

这样 bulk insert 语句就会按照 ChinaIP.xml 文件中的列映射，将平面文件 E:\ClickSun\ChinaIP.csv 的各列数据，依次插入到表 china_ip_range 的address、IPS0、IPS1、IPS2、IPS3、IPE0、IPE1、IPE2、IPE3这九个字段上了。测试了一个将近10万行的文件，执行时间在毫秒级，瞬间即操作完毕。

该文章在 2023/8/17 16:51:39 编辑过


全部评论1
	admin 2023年7月31日 22:9 相关文章参考：该评论在 2023/7/31 22:12:05 编辑过

SQLServer中采用bulk insert语句一次性插入十万级以上大量数据到指定的数据库表（字段数量不同）

全部评论1