bulk inserting uniqueidentifier column

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
1SQLNCHAR00"|\0"1IDLatin1_General_CI_AS
2SQLNCHAR00"|\0"2Field1Latin1_General_CI_AS
3SQLNCHAR00"|\0"3Field2Latin1_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(bob.speaking@.gmail.com) writes:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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. :)

Quote:

Originally Posted by

Quote:

Originally Posted by

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 :)

bobsql

Bulk insert a .NET dataset?

Hi,
Does it possible to insert an XML document generated from a Datset to SQL
Server using the SQLXML feature?
and this, without transforming the input document.
In my case this is the situation:
I've an oracle database, I'll develop a small tool that extract some data
fron this database using a VB.NET application.
From this query I'll generate a Dataset, then I'll save it as XML file.
This XML file is sent by FTP and here I want to upload these data to an SQL
Server databases.
My question is: can I upload these data without transforming the XML file?
Before I start my tests, I want to know your experience.
Thanks.
Jerome.
If you have a mapping for it, you can use bulkload to do this.
Alternativly, we also have the SqlXmlAdapter which can be used to persist
changes to a Dataset back to Sql Server.
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:OEWujqmPEHA.2236@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does it possible to insert an XML document generated from a Datset to SQL
> Server using the SQLXML feature?
> and this, without transforming the input document.
> In my case this is the situation:
> I've an oracle database, I'll develop a small tool that extract some data
> fron this database using a VB.NET application.
> From this query I'll generate a Dataset, then I'll save it as XML file.
> This XML file is sent by FTP and here I want to upload these data to an
> SQL
> Server databases.
> My question is: can I upload these data without transforming the XML file?
> Before I start my tests, I want to know your experience.
> Thanks.
> Jerome.
>

Bulk Insert + auto incremented PK

Is there a way to have a PK column auto generated while doing a bulk insert?
Thanks ya'll!!!
T.B.Yes, look at Identity column.
e.g.
CREATE TABLE Sales
(
SaleID INT IDENTITY(1,1) ,
SalesRegion CHAR(2)
)
Through Enterprise Manager, you also can change a column to be an identity
column when you modify the table structure.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Bear" <buck_roggers@.hotmail.com> wrote in message
news:odqdnbLVL_v5M_vfRVn-pg@.rogers.com...
> Is there a way to have a PK column auto generated while doing a bulk
> insert?
> Thanks ya'll!!!
> T.B.
>|||You need to use a format file. See book online for detail on how to create
one.
-oj
"The Bear" <buck_roggers@.hotmail.com> wrote in message
news:odqdnbLVL_v5M_vfRVn-pg@.rogers.com...
> Is there a way to have a PK column auto generated while doing a bulk
> insert?
> Thanks ya'll!!!
> T.B.
>

BULK INSERT

Hi,

Is there anyway to see the insert statements that are generated by a BULK INSERT statement?
I am getting an error

Bulk load data conversion error (truncation)

But the data is fine it is all characters and I checked it in a hex editor and see no issues.
Thus I want to see the actual inserts to see what it is trying to do?

Thanks in advance!

You could use OPENROWSET function with BULK options. And you could see and check loaded data before inserting:

Code Snippet

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

|||

You could also add the ERRORFILE = 'file_name' parameter in order to see which rows it's complaining about, and then take it from there.

/Kenneth