Bulk Insert Data? Data in Millions

[crossposting to microsoft.public.sqlserver.programming]
"adi" <Adityanad@.gmail.com> wrote in message
news:1129063047.140470.142060@.g44g2000cwa.googlegroups.com...
> Hi all,
> Hope there is a quick fix for this:
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into
> 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..)
>
Bad Idea.

> 2)to use waitfor - which I did but did not fix the error.
>
Bad Idea.

> 3)use bulk insert (in t-sql) - I dont know how to do this?
Won't help.

> As I see I am simply trying to move data from one table to another
> (ofcourse lots of data) in SQL Server 2000 and I dont see one simple
> solution to the locking problem.
>
You are doing the right thing. What error exactly are you getting? Do you
have a lock timeout set?
You might add a query hint to obtain an exclusive table lock on the
destination table for the duration of the load.
insert into datatable with(tablockx) (field1, field2, field3)
select a1,a2,a3 form temptable
DavidTry creating a DTS package
--
Thomas
"David Browne" wrote:

> [crossposting to microsoft.public.sqlserver.programming]
> "adi" <Adityanad@.gmail.com> wrote in message
> news:1129063047.140470.142060@.g44g2000cwa.googlegroups.com...
> Bad Idea.
>
> Bad Idea.
>
> Won't help.
>
> You are doing the right thing. What error exactly are you getting? Do yo
u
> have a lock timeout set?
> You might add a query hint to obtain an exclusive table lock on the
> destination table for the duration of the load.
> insert into datatable with(tablockx) (field1, field2, field3)
> select a1,a2,a3 form temptable
> David
>
>|||"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:E40E8E28-5C18-41E7-97FF-1AAA5352A04E@.microsoft.com...
> Try creating a DTS package
Bad idea. A single INSERT is better.
David