[freetds] Having trouble doing a BULK INSERT

Wes Gamble weyus at att.net
Mon Nov 6 15:10:00 EST 2006


I have gotten past my issue with locating the file.  However, now I get 
an error inserting the data because of the primary key column.

Here is a sample line from my data file, XXXX.upload:

832 865 4766||Wes 
Gamble||G||||Great||||||||||||||wesgamble at yahoo.com|||||||||||||||||||57192|

The last column in this table (UniqueID) is the primary key (a legacy 
table, I don't wanna hear about why the last column is the PKEY ;]). 

If I run bulk insert on an equivalent file on Windows through ODBC, it's 
fine.  It replaces the NULL value in the file with a new primary key 
value in the database.

But when I issue the following command:

    1> BULK INSERT FAXNET FROM 
'D:\FaxServe\upload_list_data\XXXX.upload' WITH (FIELDTERMINATOR='|')

via tsql, I get

    "Msg 4864, Level 16, State 1, Server DBSERVE, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 43 
(UniqueID).
(return status = -6)"

I've seen several posts regarding this.  Do I have to use a format file 
to map columns to data values in order to get this data in?  Or is there 
some IDENTITY_INSERT setting that I need to use or something on the SQL 
Server instance?

Thanks,
Wes Gamble









Lowden, James K wrote:

>>From: Wes Gamble
>>Sent: Monday, November 06, 2006 11:56 AM
>>
>>I just found freebcp - is this the correct/only way to do bulk inserts
>>    
>>
>
>  
>
>>using freetds?
>>    
>>
>
>Correctissimo.  When the data file is on client machine, you run a
>client process -- freebcp -- to load it via the TDS protocol.  
>
>When the file is on the server -- or is accessible to the server via a
>networked file system -- then you can use BULK INSERT.  BULK INSERT is a
>command to the server; the file name you indicate will be used by the
>server to open the file and read the data.  
>
>Hope that clears it up.  :-)
>
>--jkl
>
>  
>
>>Wes Gamble wrote:
>>    
>>
>>>I'm attempting to perform a BULK INSERT into a SQL Server table.
>>>I created a table named "westest" with one column in it, that's
>>>a char(10).
>>>
>>>Is there something I'm missing here - does freetds support the
>>>use of BULK INSERT?
>>>      
>>>
>>>================= Output of test ====================
>>>
>>>[dusan at exmit2 lists]$ tsql -H DBServe -p 1433 -U webuser -P webusercb
>>>locale is "en_US"
>>>locale charset is "ISO-8859-1"
>>>1> BULK INSERT dbo.westest FROM 'data'
>>>2> go
>>>Msg 4860, Level 16, State 1, Server DBSERVE, Line 1
>>>Could not bulk insert. File 'data' does not exist.
>>>1>
>>>      
>>>
>
>-----------------------------------------
>The information contained in this transmission may be privileged and
>confidential and is intended only for the use of the person(s) named
>above. If you are not the intended recipient, or an employee or agent responsible
>for delivering this message to the intended recipient, any review, dissemination,
>distribution or duplication of this communication is strictly prohibited. If you are
>not the intended recipient, please contact the sender immediately by reply e-mail
>and destroy all copies of the original message. Please note that we do not accept
>account orders and/or instructions by e-mail, and therefore will not be responsible
>for carrying out such orders and/or instructions.  If you, as the intended recipient
>of this message, the purpose of which is to inform and update our clients, prospects
>and consultants of developments relating to our services and products, would not
>like to receive further e-mail correspondence from the sender, please "reply" to the
>sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York,
>NY 10105.
>_______________________________________________
>FreeTDS mailing list
>FreeTDS at lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>  
>


More information about the FreeTDS mailing list