SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A how to - advice
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chris_Kelley
Posting Yak Master

108 Posts

Posted - 08/13/2014 :  09:34:04  Show Profile  Reply with Quote
Hi all,

I have been tasked to delete some records from a table, 252,291... yesterday I got a cursor going that sent the fields to our stored proc which removes all traces from the db....it did about 44 per minute, which is not as fast as I need...

can someone guide me in another way? this is what I have now

CREATE TABLE #TEST
(
DEBTOR_KEY NUMERIC(9,0),
DEBT_KEY NUMERIC(9,0),
AGYID VARCHAR(15),
STAT_KEY NUMERIC(9,0)
)
----------------------------------------------------
INSERT INTO #TEST
SELECT D.DEBTOR_KEY, D.DEBT_KEY, D.AGYID, D.STAT_KEY
FROM DEBT AS D WHERE D.CLIENT_KEY = 6
----------------------------------------------------
DECLARE @DEBTOR_KEY NUMERIC(9,0),
@DEBT_KEY NUMERIC(9,0),
@AGYID VARCHAR(15),
@STAT_KEY NUMERIC(9,0),
@STAT VARCHAR(1000)
----------------------------------------------------
BEGIN TRANSACTION
DECLARE TEST_CURSOR CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT DEBTOR_KEY,
DEBT_KEY,
AGYID,
STAT_KEY
FROM #TEST

OPEN TEST_CURSOR

WHILE 1=1
BEGIN
FETCH NEXT FROM TEST_CURSOR
INTO @DEBTOR_KEY,
@DEBT_KEY,
@AGYID,
@STAT_KEY

IF @@FETCH_STATUS <> 0
BREAK
SELECT @DEBTOR_KEY AS DEBTOR_KEY,
@DEBT_KEY AS DEBT_KEY,
@AGYID AS AGYID,
@STAT_KEY AS STAT_KEY
EXEC proc_DELETE_ACCOUNTS @DEBTOR_KEY,@DEBT_KEY,@AGYID,@STAT_KEY,@STAT OUTPUT
SELECT @STAT
END --END OF WHILE LOOP

CLOSE TEST_CURSOR
DEALLOCATE TEST_CURSOR
--COMMIT
--ROLLBACK
SELECT @STAT

DROP TABLE #TEST
GO

I would like to send a group of rows to the proc, like 100 or 500 at a time, is that possible?

Thanks,
Chris
Jr Programmer

Chris_Kelley
Posting Yak Master

108 Posts

Posted - 08/13/2014 :  10:36:38  Show Profile  Reply with Quote
BEGIN
--FETCH NEXT FROM TEST_CURSOR
FETCH NEXT ROWSET FROM TEST_CURSOR
FOR 100 ROWS--ROWSET-SIZE ROWS
INTO @DEBTOR_KEY,
@DEBT_KEY,
@AGYID,
@STAT_KEY

I THINK THIS WILL GET IT DONE, BUT ITS NOT PARSING, SO ANY HELP WITH THE SYNTAX IS GREATLY APPRECIATED.

Thanks,
Chris
Jr Programmer

Edited by - Chris_Kelley on 08/13/2014 10:44:00
Go to Top of Page

Chris_Kelley
Posting Yak Master

108 Posts

Posted - 08/13/2014 :  11:10:42  Show Profile  Reply with Quote
So what I am going to do is only bring in 10k records in the temp table and create a job to run nightly until they are cleared out....

Thanks,
Chris
Jr Programmer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000