471,889 Members | 2,328 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,889 software developers and data experts.

DTS/Async Stored procedure/Import huge data

I have a table which contains approx 3,00,000 records. I need to
import this data into another table by executing a stored procedure.
This stored procedure accepts the values from the table as params. My
current solution is reading the table in cursor and executing the
stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
make it better.

Can anyone help ?

Samir
Jul 20 '05 #1
2 4540
Cursors are evil!!!!

However, to be able to fully answer your question, we need to see your
stored proc/schema or even the results of a showplan.
I have used DTS/stored procs to import all sorts of data and have
never once had to resort to cursors (maybe I have just been lucky).
Generally speaking, I normally just do a select statement from the
source table which is used as input to the destination and doing any
casting/coercion along the way.

eg

create proc finky as

insert into
source
(a
,b
,c
)
select
a as char(2)
,IsNull(b, '')
,c
from
destination

sa*********@hotmail.com (Samir Pandey) wrote in message news:<33**************************@posting.google. com>...
I have a table which contains approx 3,00,000 records. I need to
import this data into another table by executing a stored procedure.
This stored procedure accepts the values from the table as params. My
current solution is reading the table in cursor and executing the
stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
make it better.

Can anyone help ?

Samir

Jul 20 '05 #2
Hey thanks for your help. but....i just can't simply insert data in
the destination table. I need to run the values thru a logic which
exists in stored procedure.(e.g. if existing price is less than the
new price, then update another field which means that the price has
reduced/increased on a particular day)... and some other i make in the
stored procedure.

Thats why i need to run the values thru a stored procedure.

now can you help...?
thanks alot....

Pr************@hotmail.com (Mystery Man) wrote in message news:<87**************************@posting.google. com>...
Cursors are evil!!!!

However, to be able to fully answer your question, we need to see your
stored proc/schema or even the results of a showplan.
I have used DTS/stored procs to import all sorts of data and have
never once had to resort to cursors (maybe I have just been lucky).
Generally speaking, I normally just do a select statement from the
source table which is used as input to the destination and doing any
casting/coercion along the way.

eg

create proc finky as

insert into
source
(a
,b
,c
)
select
a as char(2)
,IsNull(b, '')
,c
from
destination

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by JIM.H. | last post: by
7 posts views Thread by Jerry | last post: by
reply views Thread by jabernet | last post: by
3 posts views Thread by mandible | last post: by
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.