CURSOR UPDATE FOR NEW RECORD INSERTED IN BASE TABLE

Question

Post reply

CURSOR UPDATE FOR NEW RECORD INSERTED IN BASE TABLE

shantaram-815439

SSC Eights!

Points: 814
More actions
March 24, 2009 at 3:09 am

#95286

Hi,
I am working on the trade buy sell matching system for share broker. The requirement is that I am using cursor, and in the cursor I am inserting new records in the base table of the cursor. I want to run the same cursor continually for the newly added records also on recurring basis.
sample is :
/*
CREATE TABLE TMP_TEST
(ID INT, NAME VARCHAR(10))
TRUNCATE TABLE TMP_TEST
INSERT INTO TMP_TEST VALUES(1,'RAJIV1')
INSERT INTO TMP_TEST VALUES(2,'RAJIV2')
INSERT INTO TMP_TEST VALUES(3,'RAJIV3')
INSERT INTO TMP_TEST VALUES(4,'RAJIV4')
INSERT INTO TMP_TEST VALUES(5,'RAJIV5')
INSERT INTO TMP_TEST VALUES(6,'RAJIV6')
*/
DECLARE @ID INT, @NAME VARCHAR(10)
DECLARE CUR CURSOR DYNAMIC
FOR
SELECT ID, NAME FROM TMP_TEST ORDER BY ID
OPEN CUR
FETCH NEXT FROM CUR INTO @ID, @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ID
PRINT @NAME
PRINT '------------------'
INSERT INTO TMP_TEST VALUES(@ID,'RAJIV' + CONVERT(VARCHAR(10), @ID))
FETCH NEXT FROM CUR INTO @ID, @NAME
END
CLOSE CUR
DEALLOCATE CUR
This is a proforma of the cursor. The actual data is different.
So anyone pls help me out.
Thank you in anticipation.
Shantaram

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Some questions:

* Why is the usage of a cursor requirement?

* What is the reason for the recursion?

* Is the requirement that this shall be executed always when new data become inserted into your table? Trigger?

Greets

Flo

Lowell SSC Guru Points: 323518 More actions · Answer 2

the change from your real needs to your psuedo code and fake tables makes the real requirement make no sense.

in your example, you had 6 rows, and you insert the same identical 6 rows into the same table again, so there's 12 rows, 2 identical rows each with an ID between 1 and 6.

can you show us your REAL requirement? as Florian noted, there was no need to use a cursor, unless this is an educational assignment to get you used to using cursors...but your code looked fine, so I doubt that was it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shantaram-815439 SSC Eights! Points: 814 More actions · Answer 3

ORIGINAL RECORDS

------------------

lSrNo,tCltCd,tBSInd,lQty,lHedgeQty,lHedgeSrNo

1,A007,B,50,0,0

2,A007,B,155,0,0

3,A007,B,120,0,0

4,A007,S,100,0,0

5,A007,S,120,0,0

6,A007,B,50,0,0

7,A007,S,100,0,0

EXPECTED RESULT

-----------------

lSrNo,tCltCd,tBSInd,lQty,lHedgeQty,lHedgeSrNo

1,A007,B,50,50,4

4,A007,S,50,50,1

2,A007,B,120,120,5

3,A007,B,100,100,7

6,A007,B,50,50,8

5,A007,S,120,120,2

7,A007,S,100,100,3

8,A007,S,50,50,6

9,A007,B,35,0,0

10,A007,B,20,0,0

As u see in the result SrNo 1 Buy Qty is adjusted against SrNo 4 Sell Qty and updated lHedgeSrNo=1 of original buy srno, and for remaining sell qty 50 one new sell record added as srno 8. This should continue till there is no buy/sell remains for matching. See buy srno 9 and 10 as there is no sell to match these qty.

This is my main requirement.

Regards,

Shantaram