Solved

Incremental update on batch insert

Posted on 2002-06-07
13
298 Views
Last Modified: 2008-03-10
This is probably an easy one, but for some reason I cannot figure it out.

I have two tables: NewCustomer and CardNumber

NewCustomer: Holds new customer info.  Gets populated by a batch process that adds hundreds to thousands of records per batch

CardNumber: Holds all existing card numbers, both available and already assigned cards.

What needs to happen is:
When a new customer is inserted into the newcustomer table it needs to get the next AVAILABLE card number from CardNumber.  CardNumber then needs to set the available flag to 'N' for that card number.

is there an easy way to do this?
Is cursors the only option? is it an option?

Thanks

0
Comment
Question by:bsmiley
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 7

Expert Comment

by:lozzamoore
ID: 7061562
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.
0
 

Author Comment

by:bsmiley
ID: 7061565
Thanks for the suggestion.  

Unfortunately, it will be multiple rows per insert.
0
 
LVL 7

Expert Comment

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

Cheers,
0
 

Author Comment

by:bsmiley
ID: 7061606
Thanks for the suggestion.  

Unfortunately, it will be multiple rows per insert.
0
 

Author Comment

by:bsmiley
ID: 7061612
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?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 100 total points
ID: 7062010
A cursor is an option.  In fact, unforunately, it's the only I can think of that will do what you want.  For example:

CREATE TRIGGER trigger_name
ON newCustomers
FOR INSERT
AS

DECLARE csrNewCust CURSOR FOR
SELECT custNum
FROM inserted
--ORDER BY ??
FOR READ ONLY
DECLARE @custNum INT --or whatever
DECLARE @cardNum VARCHAR(20) --or whatever

OPEN csrNewCust
FETCH NEXT FROM csrNewCust INTO @custNum
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cardNum = MIN(cardNum)  --or whatever
    FROM cardNumbers
    WHERE available = 'Y'
    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
    COMMIT TRANSACTION
END --WHILE

CLOSE csrNewCust
DEALLOCATE csrNewCust
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7062016
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.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7062218
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7062308
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.
0
 
LVL 5

Expert Comment

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

=)
0
 

Author Comment

by:bsmiley
ID: 7063108
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 7063256
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
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7063463
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 . . .
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now