Posted by Erland Sommarskog on 10/10/07 21:20

(bob.speaking@gmail.com) writes:
> I'm trying to bulk insert a uniqueidentifier column from unicode file.
> In my file I have guid generated from c# application and they are
> formatted in this way (separated by "|") :
>
> guid | field1 | field2
> fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
>
> In my destination table I have three column:
> id (uniqueidentifier)
> field1 (nvarchar)
> field2 (nvarchar)
>
> I use in bulk insert a format file like this :
>
> 9.0
> 3
> 1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
> 2 SQLNCHAR 0 0 "|\0" 2 Field1
Latin1_General_CI_AS
> 3 SQLNCHAR 0 0 "|\0" 3 Field2
Latin1_General_CI_AS

Does the file really consist of one single line?

Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.

> I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
> and it works perfectly but it imports another data. For example the
> guid fc0c0c42-438e-4897-96db-8b0489e873ef became
> 00350031-0039-0033-3100-300030003000

SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.


--
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.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]