Dear Experts,

What is the best way to do a large insert WITHOUT having direct access
to the machine SQL Server is running on? For example, imagine I want
to insert something like 20,000 records. If I were to have access to
the server, I could BULK INSERT into a temp table and then insert into
the destination table. But if I can't create a file on the server to
use for BULK INSERT, what is the next best alternative to doing lots
of 1 record insert statements?

Thanks,
-Emin

On Thu, 05 Jul 2007 17:34:42 -0000, Emin <emin.shopper (AT) gmail (DOT) com>
wrote:

The Bulk Copy command line utility BCP.EXE is one alternative, as are
DTS (SQL Server 2000) and SSIS (2005) executing locally.

Roy Harvey
Beacon Falls, CT

Doesn't the client need something like SQL Management studio installed
to use DTS or SSIS? Ideally, I'd like something where the client can
just connect to the database using odbc or ado and do the insert as
opposed to requiring the client to have special software installed.

Thanks,
-Emin

On Jul 5, 5:28 pm, Roy Harvey <roy_har... (AT) snet (DOT) net> wrote:

Emin (emin.shopper (AT) gmail (DOT) com) writes:
You can use the BCP API from ODBC, although it may be a bigger programming
effort that you would like. Using command-line BCP is a lot simpler, but
then you need to make sure that it is on the client.

Another alternative is to build an XML document and pass that to a stored
procedure where unpack it. For 20000 rows, I would find this palatable.
Had you had two million rows, BCP would definitely have been the way to
go.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jul 5, 12:34 pm, Emin <emin.shop... (AT) gmail (DOT) com> wrote:
You can put your file in a shared folder - BULK INSERT can read it if
it is accessible by the account SQL Server runs under.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

You don't need the full set of client tools to run DTS packages, just
the dtsrun.exe utility. And for SSIS packages there is the dtexec
command line utility, though I have not used it. And as Erland said,
for BCP you need bcp.exe.

Roy Harvey
Beacon Falls, CT

On Thu, 05 Jul 2007 21:55:53 -0000, Emin <emin.shopper (AT) gmail (DOT) com>
wrote: