By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,854 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,854 IT Pros & Developers. It's quick & easy.

DTS/Async Stored procedure/Import huge data

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.