[freetds] Having trouble doing a BULK INSERT
Lowden, James K
james.k.lowden at alliancebernstein.com
Tue Nov 7 10:17:39 EST 2006
> >>Wes Gamble wrote:
> >>>Interesting. Is OSQL == Query Analyzer? I assume so.
No. There's an OSQL.EXE on your machine somewhere. It's similar to
isql.exe but uses the ODBC driver instead of db-lib to connect to the
The real question was: for a file that you can BULK INSERT using Query
Analyzer, can you successfully issue the same command and load the same
file using Microsoft's ISQL.EXE? If so, connection options don't
> >>>Looks like the odd option out on the Linux box is
> >>>"CONCAT_NULL_YIELDS_NULL" (value of 256) - which sounds promising.
I wouldn't think so. Bulk loading doesn't involve string catenation.
> >>>Can I modify these options for just my session or are they
> >>>database wide?
> Konrad J Hambrick wrote:
> >Since the NULL PKey field is the last field in your data, could
> >you be having a DOS (CRLF) vs *IX (LF) Line Ending issue ?
> From: Wes Gamble
> Sent: Monday, November 06, 2006 7:48 PM
> Could be. Although i've tried to "windows-ify" the file by adding
> CTRL-Ms to the end.
Konrad's idea is on target because you're having trouble with the last
field in the first row.
I'd be interested in this output:
$ head -1 datafile | hexdump -c
Your linux box surely includes something like dos2unix and unix2dos to
convert one to the other. A Windows line terminator is (hex) 0D 0A. If
you really did what you said, you wound up with 0A 0D, which won't do at
You can also use the ROWTERMINATOR option, but good luck figuring out
from the documentation how to specify a one-byte 0x0A newline
(You didn't answer my question about "equivalent". If we're not talking
about loading the *same* file, the whole discussion may be specious.)
> As it turns out though, I've decided to go with a prepared statement
> + loop insert approach, since this turns out to be much faster than
> a bulk insert anyway.
There's no chance that's true, technically. It may save you time, but
it's much, *much* less efficient than bcp or BULK INSERT. You might not
see much difference with 10,000 rows, but with 1,000,000 there's no
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,
More information about the FreeTDS