large inserts

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

Re: large inserts

On Thu, 05 Jul 2007 17:34:42 -0000, Emin
wrote:

>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

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

Re: large inserts

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

Re: large inserts

Emin (emin.shopper@gmail.com) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: large inserts

On Jul 5, 12:34 pm, Emin wrote:
> 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

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/

Re: large inserts

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
wrote:

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