Re: bulk inserting uniqueidentifier column

(bob.speaking@xxxxxxxxx) 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@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
.

Relevant Pages

  • Re: Datatype-convertion in TSQL
    ... Can't find a code for datetime-conversion from nvarchar format DD/MM/YYYY ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Bulk Import with header in text file
    ... and there is no way to specify a footer or a header. ... The one exception is when you can hide the header in the format somehow. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Real to datetime - how to...?
    ... would like to have it in hh:mm:ss format. ... The division with 24 is necessary, because a datetime value consists ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Format a datetime columns output?
    ... you could convert the date to a string? ... SQL Server you should use the format YYYYMMDD, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Update Guid column on any update to table row
    ... I don't know what the purpose with this guid is, ... data type in SQL Server, timestamp, for this purpose. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)