best bulk insert command

17 Feb 2006 10:01 AM

Sammy

I have a 5 million row table that gets truncated and new values get imported.
The new values are obtained by values that have changed in other tables.

Does anyone know the quickest way this can be acheived.

I have tried this took around 34mins

insert in attritable (attrivalue,attri_id,desc)
exec sp_insert

then

Theres a job that bcps the values out to text file in batches of 5000 ..then
inserts them into the table again in batches of 5000 and this takes around 28
mins.

Even though BCP is quicker it seems a waste to do it this way and more prone
to errors.....Is BCP definately the quickest way to enter data this way does
anyone know??

Thanks for any help or suggestions

Sammy

17 Feb 2006 11:38 AM

Dan Guzman

> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert

Instead of returning a result set that you insert, consider changing
sp_insert to create the new table with SELECT ... INTO and then create
constraints and indexes.

> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors.....Is BCP definately the quickest way to enter data this way
> does
> anyone know??

Bulk Insert methods like command-line BCP, Transact-SQL BULK INSERT, DTS and
bulk copy APIs are the fastest way to get external data into SQL Server.

> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.

This calculates to about 3000 rows per second. Not as fast as I would
expect with a narrow table on modern hardware (10,000+) but a lot depends
the size of your data and the kind of indexes you have on the table. You
may find it faster to drop indexes and recreate afterward. See Optimizing
Data Loads at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Sammy" <Sa***@discussions.microsoft.com> wrote in message
news:0B51C58B-7E10-4905-98CB-74CAA211622B@microsoft.com...
>I have a 5 million row table that gets truncated and new values get
>imported.
> The new values are obtained by values that have changed in other tables.
>
>
> Does anyone know the quickest way this can be acheived.
>
> I have tried this took around 34mins
>
> insert in attritable (attrivalue,attri_id,desc)
> exec sp_insert
>
> then
>
> Theres a job that bcps the values out to text file in batches of 5000
> ..then
> inserts them into the table again in batches of 5000 and this takes around
> 28
> mins.
>
> Even though BCP is quicker it seems a waste to do it this way and more
> prone
> to errors.....Is BCP definately the quickest way to enter data this way
> does
> anyone know??
>
> Thanks for any help or suggestions
>
> Sammy
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>