&

SQLServer中采用bulk insert語(yǔ)句一次性插入十萬(wàn)級(jí)以上大量數(shù)據(jù)到指定的數(shù)據(jù)庫(kù)表（字段數(shù)量不同）

當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流 →『技術(shù)文檔交流』

admin

2023年7月31日 21:54 本文熱度 1987

Sql server 的 bulk insert 語(yǔ)句可以高效的導(dǎo)入大數(shù)據(jù)量的平面文件（txt，csv文件）到數(shù)據(jù)庫(kù)的一張表中，其用法如下：

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

其中"tableName"是數(shù)據(jù)庫(kù)表的名字，"E:\ClickSun\ChinaIP.csv"是導(dǎo)入平面文件的地址，fieldterminator 指定平面文件中列的分隔符是什么，rowterminator 指定平面文件中行的結(jié)束符是什么。

還可以使用 FIRSTROW 和 LASTROW 限制行數(shù)。如下COPY前三行：

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

要把平面文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)表中，平面文件只有9個(gè)字段，數(shù)據(jù)庫(kù)表有12個(gè)字段，字段數(shù)量不一致，怎么把平面文件字段的對(duì)應(yīng)到表的字段，如何用 bulk insert 來(lái)實(shí)現(xiàn)？

數(shù)據(jù)庫(kù)表 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
)

平面文件數(shù)據(jù)是：E:\ClickSun\ChinaIP.csv

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

先在E盤clicksun目錄下面存放一個(gè)格式化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 語(yǔ)句時(shí)，采用 FORMATFILE 參數(shù)指定該 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 語(yǔ)句就會(huì)按照 ChinaIP.xml 文件中的列映射，將平面文件 E:\ClickSun\ChinaIP.csv 的各列數(shù)據(jù)，依次插入到表 china_ip_range 的address、IPS0、IPS1、IPS2、IPS3、IPE0、IPE1、IPE2、IPE3這九個(gè)字段上了。測(cè)試了一個(gè)將近10萬(wàn)行的文件，執(zhí)行時(shí)間在毫秒級(jí)，瞬間即操作完畢。

該文章在 2023/8/17 16:51:39 編輯過(guò)


全部評(píng)論1
	admin 2023年7月31日 22:9 相關(guān)文章參考：該評(píng)論在 2023/7/31 22:12:05 編輯過(guò)

91网首页-91网页版-91网在线观看-91网站免费观看-91网站永久视频-91网站在线播放

SQLServer中采用bulk insert語(yǔ)句一次性插入十萬(wàn)級(jí)以上大量數(shù)據(jù)到指定的數(shù)據(jù)庫(kù)表（字段數(shù)量不同）

全部評(píng)論1