SQL SERVER 使用 OPENRORWSET(BULK)函数将txt文件中的数据批量插入表中（2）

1/首先建立数据表

CREATE TABLE BasicMsg
(

IDFlag INT IDENTITY PRIMARY KEY NOT NULL, 

RecvTime FLOAT NOT NULL , --接收时间，不存在时间相同的数据
AA INT NOT NULL, --24位地址码 .
FlightID Varchar(10) NULL, --航班号
)

2/ 建立存储过程实现批量插入

USE DF17DataPro
IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[BulkDataProc]'))
DROP PROC [DBO].[BulkDataProc]
GO
--输出各个范围内NIC值
CREATE PROCEDURE BulkDataProc-- @Flag int--@SelAA INT ,@NUM_NIC6 int output,@NUM_NIC int output, @PWhole float output --,@EmptyFlag int output
AS 
SET NOCOUNT ON 
INSERT INTO BasicMsg( RecvTime,AA,CA,FlightID,MsgTypecode,unDecNum ,Longitude,Latitude, Altitude ,AltitudeType,PosUpdateFlag,VelHeading ,Velocity ,Vz,ChngInt,EmitterType,EmPriStatus ,SIL,NACP ,NACV,NIC )
SELECT * FROM OPENROWSET(
BULK 'D:\WriteDatafile.txt',
FORMATFILE='D:SqlFormat.xml'
) AS NewTable
GO

3/WriteDatafile.txt文件内容格式

43255，7867628，CCA1519

4/SqlFormat.xml文件是定义数据文件中数据字段的格式和结构

详情见：https://docs.microsoft.com/zh-cn/sql/relational-databases/import-export/xml-format-files-sql-server

格式化文件共有两种方法： XML 格式化文件和非 XML 格式化文件

（1）非格式化文件方式如下（SqlFormat.xml内容）：

8.0
3
1       SQLFLT8       0       20      ","      1     RecvTime                    Chinese_PRC_CI_AS  
2       SQLFLT8       0       20      ","      2     AA                         Chinese_PRC_CI_AS 
3       SQLCHAR       0       20      ","      3     FlightID                   Chinese_PRC_CI_AS

其中，8.0代表版本号；3代表数据文件中有几列；第一列的1,2,3表示数据文件的列的序号；而后面的第六列的1，2，3表示RecvTime,AA,FlightID在表中.是第几列

（2）格式化方式如下（SqlFormat.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="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RecvTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="AA" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="FlightID" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

不懂可参考https://msdn.microsoft.com/zh-cn/library/ms189327.aspx

5/注意事项

--格式化文件中，注意ROW 之间的COLUMN部分，

 xsi:type格式需要更改，数据列格式是什么，则更改为相应数据格式，具体格式见https://msdn.microsoft.com/zh-cn/library/ms189327.aspx链接中的COLUMN属性表后内容
--本方法本质也是BULK，只是用此方法可指定具体

只此一生，与子白头