April 13, 2011 at 4:06 pm
I have UPS Shipment tables with primary fields being [SHIPMENTNO] and [PKGNO]. If you're not acquainted with UPS, there's always a master shipment no. and if you happen to have 5 packages in that shipment, each package has a number as well. In this table I also have an autonumbered ID field.
PROBLEM: I want to add UPS freight shipments to the table. Freight shipments use just one PRO NUMBER as a unique identifier.
What I'd like to do is on insert, if there's a primary key violation, edit the [PKGNO] field to reflect [SHIPMENTNO]+[ID].
Is this possible? The INSERT table does not include the [ID] field, correct? So I have to pick it up somewhere.
thanks!
April 13, 2011 at 4:09 pm
Without seeing the table schema this is hard to answer.
If you're going to replace items in the current identity column, you'll have to turn off the identity component (and if I'm understanding your intent, the unique as well) and probably use and external sequencer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 14, 2011 at 12:09 pm
shank-130731 (4/13/2011)
What I'd like to do is on insert, if there's a primary key violation, edit the [PKGNO] field to reflect [SHIPMENTNO]+[ID].
That's a bad idea. Why not make ID the primary key and just put the appropriate indexes on the other 3 fields?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply