BCPing data into specific columns in a table

[jones33]

Hi,

I have a table with 15 columns. However, in my data file I only have 9 columns. I have created a format file to map the data fields to the relevant columns. Each time I BCP data into the table though it just inserts the data into the first 9 columns regardless of the fact that I have mapped the fields to other columns.

My Fomat file:
<?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="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>

Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data from fields 7,8 and 9 in the file are still being inserted into columns 7,8 and 9 in the table.

What I also notice is that if I change the "NAME" in the column element to something other than the name of the column in the table then it doesn't cause an error.

It isn't using the column name then when doing the BCP in.

The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test\TestFormat.fmt -T -t

HOWEVER, if I use the following command:
INSERT INTO testTable (col1 .... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the BULK INSERT method will write to the transaction log ... not good with the volumes of data I am dealing with.

Does anyone have any ideas of what I am doing wrong here?

Any ideas / suggestions would be much appreciated.

Many Thanks