Inserting large amount of rows?  
Author Message
editchick





PostPosted: Mon Apr 09 15:41:33 CDT 2007 Top

SQL Server >> Inserting large amount of rows?

Environment: SQL 2000 Standard SP4
Database recovery mode: Simple

I have a stored proc that once a day transfers a large amount of data
(2+ million rows usually) from one table to another as follows:

INSERT INTO table1
SELECT * FROM table2

DROP table2

This doesn't scale well obviously, and quite often the above operation
results in "could not obtain lock" error.

Could someone suggest, please, some less brutal way of copying rows,
like in batches, so example? None of the tables have primary keys or
identity columns, by the way.

TIA!

SQL Server107  
 
 
Greg





PostPosted: Mon Apr 09 15:41:33 CDT 2007 Top

SQL Server >> Inserting large amount of rows?

> Environment: SQL 2000 Standard SP4
> Database recovery mode: Simple
>
> I have a stored proc that once a day transfers a large amount of data
> (2+ million rows usually) from one table to another as follows:
>
> INSERT INTO table1
> SELECT * FROM table2
>
> DROP table2
>
> This doesn't scale well obviously, and quite often the above operation
> results in "could not obtain lock" error.
>
> Could someone suggest, please, some less brutal way of copying rows,
> like in batches, so example? None of the tables have primary keys or
> identity columns, by the way.

You need to have some form of index so you can order the data.

Then you can insert it in ranges.

And as Celkowould point out, w/o a primary key, you don't really have a
table, just a collection of data.


>
> TIA!

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html


 
 
Uri





PostPosted: Tue Apr 10 02:36:51 CDT 2007 Top

SQL Server >> Inserting large amount of rows? Hi
Do you perform this operation during the work hours? Try to divide the batch
into small batches i.e inserting rows with four batches as 500000 instead
of 2mln



> Environment: SQL 2000 Standard SP4
> Database recovery mode: Simple
>
> I have a stored proc that once a day transfers a large amount of data
> (2+ million rows usually) from one table to another as follows:
>
> INSERT INTO table1
> SELECT * FROM table2
>
> DROP table2
>
> This doesn't scale well obviously, and quite often the above operation
> results in "could not obtain lock" error.
>
> Could someone suggest, please, some less brutal way of copying rows,
> like in batches, so example? None of the tables have primary keys or
> identity columns, by the way.
>
> TIA!