Execute two stored procs for 250000 records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execute two stored procs for 250000 records

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2005-11-16 : 11:40:10

Hello, anyone have a suggestion on the best way to do this? I need to run two procs against about 250,000 records, basically the first does a move of the records and performs some calculations while doing so, then the second deletes the records. 10 tables are involved. If I run these sequentially against a record they work fine, but how to do for many many records?

Do I need a cursor to do this? I'd like to do it from Query Analyzer.

Thanks!

LW

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-16 : 12:03:55

1. unlikely to require a cursor.
2. post some sample input data, processing required and (matching) expected results and we'll see what can be done for you. also useful to post some ddl, and data in 'insert statements' style.

littlewing
Starting Member

33 Posts

Posted - 2005-11-16 : 12:39:35

Ok, very rough here but here's two of the tables:

tblCart

CartID int
UserID int
CreateDate datetime

tblAbandonedCart

CartID int
UserID int
CreateDate datetime

Would like to do the following in pseudocode:

exec spAbandonCart, exec spDeleteCart WHERE CartID in (SELECT CartID FROM tblCart)

spAbandonCart moves data from Cart tables to AbandonedCart tables and then spDeleteCart removes that Cart from tblCart. First proc does all the necessary INSERT's into the abandoned cart tables, second proc has all the necessary delete statements.

Thanks!

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 15:42:01

Would something like this do?


DECLARE @tblCartList TABLE
(
    T_CartID int NOT NULL,
    PRIMARY KEY
    (
        T_CartID
    )
)

-- Make list of the ID's we need to process
INSERT INTO @tblCartList
SELECT [T_CartID] = CartID
FROM tblCart

INSERT INTO tblAbandonedCart
(
    CartID, UserID, CreateDate
)
SELECT CartID, UserID, CreateDate
FROM @tblCartList
     JOIN tblCart
         ON CartID = T_CartID

DELETE D
FROM SELECT CartID, UserID, CreateDate
FROM @tblCartList
     JOIN tblCart AS D
         ON CartID = T_CartID

Kristen

littlewing
Starting Member

33 Posts

Posted - 2005-11-16 : 15:58:46

Thanks Kristen, I guess my example was oversimplified, there are another 8 tables involved. I have all the necessary SQL in the two stored procs plus there are some calculations going on as the carts are transferred to the abandoned cart table.

All I want to do is execute these two procs in a loop.
for each cartID in tblCart
exec spAbandonCart(cartID)
exec spDeleteCart(cartID)
next

I was going to use a cursor but thought there might be a better way.

Thanks.

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 16:03:18

"I have all the necessary SQL in the two stored procs ..."

OK, understood, but looping round is much slower (cursor, or not!) than a single UPDATE statement (as per my example, albeit overly simplistic compared to your real world scenario)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources