[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