Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CURSOR UPDATE FOR NEW RECORD INSERTED IN BASE TABLE Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 3:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 07, 2013 3:03 AM
Points: 14, Visits: 66
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
Post #682148
Posted Tuesday, March 24, 2009 10:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #682531
Posted Tuesday, March 24, 2009 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #682542
Posted Tuesday, March 24, 2009 11:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 07, 2013 3:03 AM
Points: 14, Visits: 66
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
Post #683052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse