Efficiently Inserting 1 Million records

I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:

> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan