|
|
Posted by Erland Sommarskog on 10/12/05 00:53
adi (Adityanad@gmail.com) writes:
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
>
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)
There are a couple of techniques. But for best performance, you need
a clustered index in the table. This does not have to be unqiue though.
Say that you have a date column in the table that has a decent distribution.
Create a clustered index on tbat column:
CREATE CLUSTERED INDEX ON temptable(datecol)
Then you can do:
DECLARE @this_date datetime,
@next_date datetim
SELECT @this_date = MIN(datecol) FROM temptable
WHILE @this_date <= (SELECT datecol FROM temptable)
BEGIN
SELECT @next_date = dateadd(MONTH, 1, @this_date)
INSERT dataable (...)
SELECT ...
FROM temptable
WHERE datecol >= @this_date
AND daetcol < @next_date
SELECT @this_date = @next_date
END
It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.
If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.
You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)
> 2)to use waitfor - which I did but did not fix the error.
I will have to admit that I don't see the point with WAITFOR here.
> 3)use bulk insert (in t-sql) - I dont know how to do this?
This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.
Then again, your also try:
INSERT datatable (...) WITH (TABLOCKX)
SELECT ...
I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|