This is a repost, I accidentally posted to .programming by mistake (it's
been a long day already).  Thanks.

-------------

Having an issue with bcp on 2005, but brain is fried today for some reason.
Any help appreciated.  Here's the deal:

I have an XML format file like this:

<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"
COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="10"
COLLATION="Latin1_General_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="ReportingDate" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="PolicyID" xsi:type="SQLVARYCHAR"/>
</ROW>

The source file looks like this:

2007-10-10|ABC123
2007-10-10|DEF456
....

This works great for a table with 2 columns (ReportingDate, PolicyID).  When
I add an IDENTITY column, so the destination table looks like this (for
example):

ID   INT IDENTITY(1, 1) NOT NULL,
ReportingDate   VARCHAR(50),
PolicyID    VARCHAR(50)

Bcp and BULK INSERT break when I try to use the format file with the new
IDENTITY column.  I've gotten around this problem before, but I'll be darned
if I can remember how.  It's probably really simple, but I'm a little out of
it this morning.  If anyone has any hints, it's appreciated.

Thanks

Mike C# (x**@xyz.com) writes:
Show quoteHide quoteIf the IDENTITY column is the first in the table, you need to change COLUMN
SOURCE="1" to COLUMN SOURCE="2" etc. I never use the XML format file, but in
the old-style format file the colunm with the column names is informational
only. It's the column number that matters.

Show quote Hide quote I did go back and generate the old-style format files, which worked.  I'll
test the XML format file tomorrow with the SOURCE attributes bumped up by 1
each to see if that resolves it.  For some reason I was thinking the XML
format file would use the column names instead of ordinal position, but it
makes sense that they wouldn't since the old BCP format didn't either.
Thanks Erland.

Mike C# (x**@xyz.com) writes:
Books Online says:

   Specifies the name of the column in the row set represented by the
   format file. This column name is used to identify the column in the
   result set, and it need not correspond to the column name used in the
   target table.

I suppose that they have OPENROWSET(BULK) in mind.

Personally, I stick to the old-style. It took me many years to understand
all fields now, but I know speak that format fluently. I'm not giving it
up!

SQL2k Profiler: load big trace file into table, on small system ?
BCP with a query in a file, or SQLCMD outputting tab-delimited?
SQL Server 2000 EM vs. SQL Server 2005 MS
where can I get "Microsoft SQL Server Migration Test Toolkit" ?
Database Script Does Not Account for Dependencies