BCPing into a Table with more columns than the source data file

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 fields 7,8 and 9 in the data file to other columns in
the table.
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.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many Thanks
On Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> 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 fields 7,8 and 9 in the data file to other columns in
> the table.
> 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.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed
|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegro ups.com...
> 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.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx