[freetds] Having trouble doing a BULK INSERT

Wes Gamble weyus at att.net
Mon Nov 6 16:47:38 EST 2006


Weird -

I can create a table with two columns, the last of which is an identity 
field and bulk insert into this table using tsql without a problem.

Wes

Wes Gamble wrote:

>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
>>
>> 
>>
>>    
>>
>_______________________________________________
>FreeTDS mailing list
>FreeTDS at lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>  
>


More information about the FreeTDS mailing list