bulk insert批量数据导入

在SQL Server中，BULK INSERT是用来将外部文件以一种特定的格式加载到数据库表的T-SQL命令。该命令使开发人员能够直接将数据加载到数据库表中，而不需要使用类似于Integration Services这样的外部程序。虽然BULK INSERT不允许包含任何复杂的逻辑或转换，但能够提供与格式化相关的选项，并告诉我们导入是如何实现的。BULK INSERT有一个使用限制，就是只能将数据导入SQL Server。

 

 

1、  要使用bulk insert需要具备三个条件

a)         单表数据导入

b)         存放数据的文件

c)         描述存放数据的文件格式的格式化文件

d)         Bulk insert语法

 

2、  Bulk insert可以对单表数据进行批量导入，速度非常快

 

3、  一般采用txt文件存放数据，列与列之间，组与组之间采用不同的分隔符分隔，

如：

Aaa{,}bbb{,}ccc{,}ddd

Xxx{,}yyy{,}zzz{,}ddd

以上，列与列的分隔符为{,}，组与组的分隔符为\r\n（注意：分隔符最好要比较特殊，以免造成错误）

 

4、  格式化文件，xml文件

如：

<?xmlversion="1.0"?>

<BCPFORMATxmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <RECORDxmlns=""> <!—记录-->

    <FIELDID="1"xsi:type="CharTerm"TERMINATOR="{,}" />   <!—第几列、数据类型、结束符-->

    <FIELDID="2"xsi:type="CharTerm"TERMINATOR="{,}" />

    <FIELDID="3"xsi:type="CharTerm"TERMINATOR="{,}" />

    <FIELDID="4"xsi:type="CharTerm"TERMINATOR="{,}" />

    <FIELDID="5"xsi:type="CharTerm"TERMINATOR="{\r\n}" />    <!—注意最后一列的结束符，一般为\r\n-->

  </RECORD>

  <ROWxmlns="">        <!—对上面的列命名并设置数据类型-->

    <COLUMNSOURCE="1"NAME="receivePhoneNum"xsi:type="SQLVARYCHAR" /> <!—第几列（对应上面的第几列）、列明、数据类型-->

    <COLUMNSOURCE="2"NAME="preSendTime"xsi:type="SQLDATETIME" />

    <COLUMNSOURCE="3"NAME="addTime"xsi:type="SQLDATETIME" />

    <COLUMNSOURCE="4"NAME="smsContentId"xsi:type="SQLINT" />

    <COLUMNSOURCE="5"NAME="shortName"xsi:type="SQLVARYCHAR" />

  </ROW>

</BCPFORMAT>

 

5、  Bulk insert语法

如：

insert into dbo.smsSend_t_send(receivePhoneNum, preSendTime, addTime, smsContentId, shortName)

select receivePhoneNum, preSendTime, addTime, smsContentId, shortName

from openrowset(

     bulk 'D:\SmsSend\hz.txt'

    ,formatfile='D:\SmsSend\formatfile.xml'

)txt

 

         D:\SmsSend\hz.txt  是数据存放的文件的路径

    D:\SmsSend\formatfile.xml   是解析上面文件的格式化配置文件