bulk inserting uniqueidentifier column

[bob.speaking]

Hi at all,
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

and I use this script

BULK INSERT [dbo].[KWTA2] FROM 'd:\WTA2.txt'
WITH (FORMATFILE = 'd:\wta2Format.FMT')

It doesn't work, it prints out
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.

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

Please can you help me?
Why sql converts alphanumerical GUID into only numbers ID?
How can I bulk insert GUID? (I didn't find anything googling around :
\ )

Thanks!

Bob

[Erland Sommarskog]

(bo**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[bob.speaking]

>

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'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)

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.

I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?

Thanks for the prompt reply :)

bob