[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

BCPing Data into a table with more columns than are in the source data file

scudi54

3/31/2007 12:03:00 AM

Sorry, posted this question on the SQLServer.Server group as well.
Think this one is probably more suitable though. New to google groups.
My apologies


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/sqlse...
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
1 Answer

Erland Sommarskog

4/1/2007 7:23:00 PM

0

(scudi54@yahoo.com) writes:
> 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.
>...

Maybe there is some big gain with XML format files somewhere in a
future version, but for SQL 2005, I have not been able to find any.
And browsing Books Online I was not able to make out how to this.

Yet it's as simple as 1-2-3 with the old style format files:

9.0
9
1 SQLDATETIME 0 8 "" 1 col1 ""
2 SQLINT 0 4 "" 2 col2 ""
3 SQLINT 0 4 "" 3 col3 ""
4 SQLINT 0 4 "" 4 col4 ""
5 SQLINT 0 4 "" 5 col5 ""
6 SQLFLT8 0 8 "" 6 col6 ""
7 SQLFLT8 1 0 "" 13 col13 ""
8 SQLFLT8 1 0 "" 14 col14 ""
9 SQLFLT8 1 0 "" 15 col15 ""

What matters are the numbers in the sixth column. They specify to
table column the field in the data file maps to. The following column
is the column name, but this is informational only and BCP/BULK INSERT
does not use it.

It looks somewhat funny that an SQLFLT8 value would be stored using a
prefix, but I guess it's alright.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...