bulk insert optimization

Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
AjeyOne thing you can do is to have the file sorted in the same order as the
primary key or clustered index, and then specify the ORDER parameter of BULK
INSERT command. Have you read about it?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Ajey" <ajey5@.hotmail.com> wrote in message
news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
Hi,
I need to use the BULK INSERT for pushing data into two of the tables
(PK=datetime + int + int). I had read about the optimizations that can be
done for this. But since my tables will keep growing with every inserts (in
GBs), droping & creating the indexes (clustered and non-clustered) itself
can be time comsuming. Are there other options for optimizations?
TIA
Ajey|||Yes.
Instead of bulk inserting the data directly to the target table what i am
planing to do is:
- copy the file to the target sql server
- bulk insert into a temp table (same schema but no indexes)
- insert into the target table from the temp table
will this be efficient?
TIA
- Ajey
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...
> One thing you can do is to have the file sorted in the same order as the
> primary key or clustered index, and then specify the ORDER parameter of
> BULK
> INSERT command. Have you read about it?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%235f4bHtsFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I need to use the BULK INSERT for pushing data into two of the tables
> (PK=datetime + int + int). I had read about the optimizations that can be
> done for this. But since my tables will keep growing with every inserts
> (in
> GBs), droping & creating the indexes (clustered and non-clustered) itself
> can be time comsuming. Are there other options for optimizations?
> TIA
> Ajey
>
>|||Ajey <ajey5@.hotmail.com> wrote:
> Yes.
> Instead of bulk inserting the data directly to the target table what
> i am planing to do is:
> - copy the file to the target sql server
> - bulk insert into a temp table (same schema but no indexes)
> - insert into the target table from the temp table
> will this be efficient?
You should measure it for a definite answer. Personally I don't believe
this will be faster than a direct bulk load but you never know.
robert
> TIA
> - Ajey
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:upHv44tsFHA.3040@.TK2MSFTNGP14.phx.gbl...