Doni,
If you are doing this as a single INSERT INTO SELECT FROM statement, then
the whole thing is a single transaction and that will certainly affect other
users.
If you could revise your code to insert (for example) about 10,000 rows at a
time, then each insertion would be much shorter. You would, of course, need
to keep track of which row was last inserted and loop through your data
getting the next 10,000 until you finish.
Of course, don't put a BEGIN TRAN / COMMIT TRAN around the loop or you are
back at a single transaction.
(If you decided to bcp out then back in (which I don't really recommend)
then you should use the -b option to set the batch size to (again for
example) 10,000.)
RLF
[quoted text, click to view] "Doni Devito" <Doni@Devito.com> wrote in message
news:eeyh$q0kHHA.3472@TK2MSFTNGP04.phx.gbl...
> hi,
> I had to insert huge amount of data from one table to another. Im doing
> this 3-5 times a day and I had to do it in stored proc. In one insert Im
> copying 2-3 millions rows. I HAD to use stored proc.
> 1.Im selecting a recordset from 4 tables.
> 2.Insert them to a table.
> 3.Table has 2 columns. Both interer and Primary key.
> 4.there are about 500 millions rows in destination table and will stop the
> increase in 800-900 million.
>
> i can do it in one single select/insert statement. but when i start it, it
> locks records and takes 4 hours around. At first the time is not important
> but it locks the table and the other sesssions selecting from this table
> begins to wait. I looked for bcp, bulk insert routines but cant figure out
> how must i do. As i said before, i had to do it in stored procedure. Is
> there any easy way? or i will bcp out to a table and will bcp in again.
>
> thanks.
>
> --
> Open your mind
> Deny ignorance
> Demand truth
>
