Bulk Insert error trying to insert a GUID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk Insert error trying to insert a GUID

Author

Topic

sherrer

64 Posts

Posted - 2002-11-19 : 12:03:53

I am trying to populate the following table by using the BULK INSERT statement.

CREATE TABLE [dbo].[InvoiceHistPreviewDetails] (
[BillProcessID] [id] NOT NULL ,
[Seq] [int] NOT NULL ,
[AcctID] [id] NOT NULL ,
[Amt] [money] NOT NULL ,
[RevCode] [code] NOT NULL
) ON [PRIMARY]

Here are a few lines of the text file:

"{F42A322A-D0FB-46F2-8B89-7AA5578910CD}",0,"{E4D96E00-8512-11D5-B7E6-0020B32F3F49}",9.01,"RUBG"
"{F42A322A-D0FB-46F2-8B89-7AA5578910CD}",1,"{E4D96E00-8512-11D5-B7E6-0020B32F3F49}",20.26,"RUBW"
"{F42A322A-D0FB-46F2-8B89-7AA5578910CD}",2,"{E4D96B02-8512-11D5-B7E6-0020B32F3F49}",9.01,"RUBG"
"{F42A322A-D0FB-46F2-8B89-7AA5578910CD}",3,"{E4D96B02-8512-11D5-B7E6-0020B32F3F49}",20.26,"RUBW"

Using the following statement:

bulk insert InvoiceHistPreviewDetails from '\\sherrer\test\kevin.txt' With (FIELDTERMINATOR = ',')

produces the following error:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 1 (BillProcessID).
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

I am assuming now that I have to use a format file to define the datatypes, but I can not get a format file to work.

This is the current file I am working with, but I have attempted many different ways.

7.0
5
1 SQLUNIQUEID 0 0 "\",\"" 1 BillProcessID ""
2 SQLINT 0 4 "\",\"" 2 Seq ""
3 SQLUNIQUEID 0 0 "\",\"" 3 AcctID ""
4 SQLMONEY 0 8 "," 4 Amt ""
5 SQLCHAR 0 4 "\"" 5 RevCode ""

... The format of the file probably won't look right after pasting ...

The following error is created after trying to execute the bulk insert statement.

Server: Msg 4823, Level 16, State 1, Line 1
Could not bulk insert. Invalid column number in format file '\\sherrer\test\test.fmt'.

If anyone has any suggestions, please let me know.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-11-19 : 12:37:36

I think you're problem has to do with the datatype you are declaring for BillProcessID. Can you change that to a varchar and put a primary key on that column? I do not think you need a format file. Also, you might have problems with the AcctID column. Is id a user-defined data type?

***************************************
Death must absolutely come to enemies of the code!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-19 : 13:00:15

bcp likes uniqueidentifiers to look like
F42A322A-D0FB-46F2-8B89-7AA5578910CD
without the "{}".

You can import them into varchar fields and convert or find some way of stripping these characters off.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sherrer

64 Posts

Posted - 2002-11-19 : 13:14:16

It works fine without the brackets and quotes.

Thank you nr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources