Skip to Content.
Sympa Menu

freetds - Re: [freetds] Having trouble doing a BULK INSERT

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Wes Gamble <weyus AT att.net>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Having trouble doing a BULK INSERT
  • Date: Mon, 06 Nov 2006 15:47:38 -0600

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@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






Archive powered by MHonArc 2.6.24.

Top of Page