Incremental update on batch insert

When you say per batch, how are the inserts running, one at a time, or multiple rows per statement?

If one at a time, one non cursor option you might want to consider is writing an insert trigger on the NewCustomer table.

The trigger would be written something similar to the following:

create trigger insert_newcust on newcustomer
for insert
as

declare @new_card_no <datatype for cards>
begin tran
insert cardnumber ....(including available='N')
select @new_card_no=@@identity

update inserted set card_no=@new_card_no
commit tran


Hope this helps.

Thanks for the suggestion.  

Unfortunately, it will be multiple rows per insert.

How are card numbers assigned in the cardnumbers table?
Via an identity column, or some other unique algorithm?

Cheers,

Thanks for the suggestion.  

Unfortunately, it will be multiple rows per insert.

The card number itself is the key.

The table gets loaded from a flat file as new cards are received.

Does this answer your question?

Naturally you will need, and presumably already have, indexes on custNum and cardNum.  I would add the available flag to the cardNum index, so that an access to the db won't be required to determine the status -- that should speed up the processing.  However, updating 100s of 1000s of rows via a cursor won't be fast at any rate.  Still, I can't think of another way right now to do what you want.

Is there a Problem with adding the customer number field to the CardNumber table.  This will allow us to avoid the cursor:


CardNumber (CardNumber, Available, CustomerNumber)
Customer (CustomerNumber, CardNumber)

Create Trigger On Customer For insert As

Insert Into CardNumber
(Available, CustomerNumber)
Select 'N', CustomerNumber
FROM Inserted

Update CU
Set CardNumber = CD.CardNumber
FROM Customer CU Inner Join CardNumber CD
    ON CU.CustomerNumber = CD.CustomerNumber
    Inner Join Inserted I
    ON CU.CustomerNumber = I.CustomerNumber

go



Here's the code to add the field to the table

--Add the column
Alter Table CardNumber
Add CustomerNumber int

-- Update for all the previous records
Update CD
Set CardNumber = CU.CardNumber
From CardNumber CD Inner Join Customer CU
ON CD.CustomerNumber = CU.CustomerNumber

spcmnspff:

I think we have different understandings of what is going on.  I didn't think card numbers were being generated but had been pre-determined and the available (and already assigned) numbers loaded into the CardNumber table.  Your sample looks as if you believe new card numbers are being generated.  Perhaps bsmiley can clarify which is the actual situation he/she is dealing with.

True, I assumed that CardNumber.CardNumber was a generated number by either an Identity or a UDF default field . . .

=)

Scott is correct.

We get two things from an agreement with a vendor:

1. a Shipment of pre-printed cards that arrives at our warehouse.

2. a flat file that details the card numbers, shipment box, etc. that they sent to us.

When we receive the flat file it will be loaded into the master card table making it available for us to issue those cards to our new customers.

From there the above process picks up.

Does this explain it properly?  

Also, based off my (brief) explanation of tables and process could there be a design flaw?  Any help is appreciated.

Personally, I don't know that I see a better way to do it.  Sometimes a given amount of processing is required to complete a business function.

I did think of one refinement.  You could reduce the overhead by combining multiple sets of updates in one transaction, like so:

DECLARE @updates_per_commit TINYINT
DECLARE @update_count TINYINT
SET @updates_per_commit = 10 --or whatever
SET @update_count = 0
BEGIN
   SET @update_count = @update_count + 1
   SELECT @cardNum = MIN(cardNum)  --or whatever
   FROM cardNumbers
   WHERE available = 'Y'
   IF @update_count = 1
       BEGIN TRANSACTION  
   --using TRAN to make sure that once custNum is
   --assigned to customer it is always marked unavailable
   UPDATE customers
   SET cardNum = @cardNum
   WHERE custNum = @custNum
   UPDATE cardNumbers
   SET available = 'N'
   WHERE cardNum = @cardNum
   IF @update_count = @updates_per_commit
   BEGIN
       COMMIT TRANSACTION
       SET @update_count = 0
   END --IF
END --WHILE

Here's an interesting one  . . .

Create Trigger trCustomerCard On Customer Instead of Insert as

Select I.CustNum, C.CardNum, I.CustNum + C.CardNum Art,
Into @TempTable
FROM Inserted I, Cards C
WHERE C.Available = 'Yes'

Insert Customer
Select T1.CustNum, T1.CardNum
from @TempTable T1 Left Join @TempTable L
 ON T1.CustNum = L.CustNum
    AND T1.Art > L.Art
WHERE L.CustNum Is Null

Update Cards
Set Available = 'No'
From Cards, Inserted I
Where Cards.CustNum = I.CustNum
GO


The temp table is a combination of all the new customer numbers and all the available card numbers form a cross join.  Then the insert finds the custnum and cardnum with the lowest combination, which is unique for each customer because each custnum is unique.  Then the update swithces the used Card records off.  Here I'm assuming that the custnum and cardnum fields are of the same datatype.  If not, you can convert one to the other before combining them.  This should work whether dealing with numeric or text fields.

Anything to avoid a cursor . . . ;-)  Actually I would probably use the cursor here . . . . reluctantly . . .