February 10, 2005 at 2:31 pm
It inserts all the records first, say there were 1000 records. It then gets SCOPE_IDENTITY() (which will return 1000) and then updates the tblSponsor.
Instead, what I want is to execute these steps per record. That way each record that gets created will return a new SCOPE_IDENTITY() so I can update tblSponsor correctly.
February 10, 2005 at 3:01 pm
are you using tblxxx as a kind of temp table?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 10, 2005 at 3:04 pm
yes..it has no pk defined
February 10, 2005 at 3:16 pm
I am getting an error on:
update tblSponsor set
intAddressID = (select a.intAddressID
FROM tblAddress as a, tblxxx x
WHERE ((x.SponsorID = CAST(a.nvcASLMU AS int))
and (a.nvcASCU = 'aaa')))
saying:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.
February 10, 2005 at 3:26 pm
Use a cursor to loop through tblxxx and do the insert and update within the cursor fetch loop.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply