[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