Re: Bcp problem (2005), previously posted to microsoft.public.sqlserver.programming
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2007 22:08:11 +0000 (UTC)
Mike C# (xyz@xxxxxxx) writes:
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.
If 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.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- References:
- Prev by Date: Re: BCP and CSV - my conclusions
- Next by Date: Re: SQL2k Profiler: load big trace file into table, on small system ?
- Previous by thread: Bcp problem (2005), previously posted to microsoft.public.sqlserver.programming
- Next by thread: Re: Bcp problem (2005), previously posted to microsoft.public.sqlserver.programming
- Index(es):
Relevant Pages
|
