Bcp problem (2005), previously posted to microsoft.public.sqlserver.programming

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



.

Relevant Pages

  • Re: bcp problem
    ... You could try using a format file, where the column delimiters would be ... qualifiers be part of the delimiter and thus get stripped out. ... If you want to stick with bcp, then just try using the Bulk Insert Task, as ... > f text 16 nulls ...
    (microsoft.public.sqlserver.dts)
  • Re: Create Job to dump SP results into a text file.
    ... I am glad to hear that the osql works fine now. ... I use BCP to create a format file for the output (this could be ... ', type a name to generate the format file, I use ... create a table 'authorsx' to hold the result set of the stored ...
    (microsoft.public.sqlserver.programming)
  • Re: Create Job to dump SP results into a text file.
    ... > I am glad to hear that the osql works fine now. ... > 2) using BCP to save the content of this table to a tab limited log file ... ', type a name to generate the format file, I use ... create a table 'authorsx' to hold the result set of the stored ...
    (microsoft.public.sqlserver.programming)
  • Re: BCP Troubles
    ... The SQL server we are tying to BCP into is SQL 2000. ... > way I got it to work was to run BCP and have it create a ForMaT file. ... So it sounds like the first time BCP is not executed at ...
    (comp.databases.ms-sqlserver)
  • Re: bcp and order of rows in table
    ... How about adding an identity column, letting SQL Server generate the ... I am loading data from external program using bcp into a temp table. ... > process records in the order they are in the file. ...
    (microsoft.public.sqlserver.tools)