测试表:
create table p_stocks(idkey int identity(1,1),cpcode varchar(20)default(''),cpname varchar(20) default(''),cpmodel varchar(20) default (''),cunit varchar(10) default (''))
测试数据(txt文件):
编码,名称,车型,单位
test1,test1,比亚迪,个
test2,test2,奇瑞,片
test3,test3,天籁,双
sql语句:
BULK INSERT p_stocks FROM 'c:\test.txt'
WITH
(
formatfile='c:\test.xml',
--FIELDTERMINATOR=',',
--ROWTERMINATOR='\r\n'
FIRSTROW=2
)
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="," />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cpcode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="cpname" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="cpmodel" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="cunit" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
问题:导入时候不会跳过idkey来对应字段导入(如下图1所示,正确的应该是图2),请教大神们xml文件怎么写才可以...
图1
图2