TOUGH INSERT: Copy Sale Record/Line Items for "Duplicate" Record

[Maria L]

I have a client who needs to copy an existing sale. The problem is
the Sale is made up of three tables: Sale, SaleEquipment, SaleParts.
Each sale can have multiple pieces of equipment with corresponding
parts, or parts without equipment. My problem in copying is when I go
to copy the parts, how do I get the NEW sale equipment ids updated
correctly on their corresponding parts?

I can provide more information if necessary.
Thank you!!

Maria

[David Portas]

You haven't posted much information so I'll have to make some assumptions
about your table structures, keys and constraints. Please include DDL with
your posts so that we don't have to guess:

CREATE TABLE SaleEquipment (seqid INTEGER IDENTITY PRIMARY KEY, orderno
INTEGER NOT NULL /* REFERENCES Sales (orderno) */, eq_partno INTEGER NOT
NULL /* REFERENCES Parts (partno) */, qty INTEGER NOT NULL, UNIQUE
(orderno,eq_par tno))

CREATE TABLE SaleParts (seqid INTEGER NOT NULL REFERENCES SaleEquipment
(seqid), partno INTEGER NOT NULL /* REFERENCES Parts (partno) */, qty
INTEGER NOT NULL, UNIQUE (seqid,partno))

This looks to me like a hirerarchy parts explosion in which case I'm not
sure why two tables are necessary. Is "Equipment" not just an inventory item
that has constituent "Parts" that are also inventory items? If the
constituent parts don't change then it could all reside in a single Parts
table.

Here's what you wanted to do. First, the SaleEquipment for the new sale:

INSERT INTO SaleEquipment (orderno, eq_partno, qty)
SELECT @new_orderno, eq_partno, qty
FROM SaleEquipment
WHERE orderno = @orderno /* The order you are duplicating */

Now insert the parts:

INSERT INTO SaleParts (seqid, partno, qty)
SELECT E2.seqid, P.partno, P.qty
FROM SaleParts AS P
JOIN SaleEquipment AS E1
ON P.seqid = E1.seqid
JOIN SaleEquipment AS E2
ON E1.orderno = @orderno
AND E2.orderno = @new_orderno
AND E1.eq_partno = E2.eq_partno

Note the key on SaleEquipment (orderno,eq_par tno) which I've just assumed.
If you don't have a natural key in your table then you have problems - but
hopefully you knew that anyway.

Hope this helps.

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> The problem is the Sale is made up of three tables: Sale,
SaleEquipment, SaleParts. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Each sale can have multiple pieces of equipment with corresponding parts, or parts without equipment. <<

This can be done with an inventory table that has all the items for
sale, both parts and complete units of equipment. Then you have a
separate BOM (bill of materials) table that gives the parts explosion
for the equipment.
... how do I get the NEW sale equipment ids updated correctly on

their corresponding parts? <<

I am not quite sure what you want. It sounds like you take a bunch of
parts, then look for an assembly made from them and return the
inventory number and name for the assembly. Example: I order pants
#1, jacket #3 and vest #2 as separate items with separate prices. The
system looks out for my best interest and sees that such a combination
is available as "Three piece suit #123", replaces my three items with
this one item, and then bills me the lower price of the suit.

The code is a little tricky, but it is basically an exact relational
division done with a nested set model of the parts explosion.

Or did you mean something else?

[Maria L]

Thanks for the feedback. Here's the "more information" you requested.
CREATE TABLE [dbo].[Sale] (
[SaleID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NULL ,
[SaleDate] [datetime] NULL ,

) ON [PRIMARY]
CREATE TABLE [dbo].[SaleLineEquipme ntItem] (
[SaleLineEquipIt emID] [int] IDENTITY (1, 1) NOT NULL ,
[SLSaleID] [int] NOT NULL ,
[SLEquipmentID] [int] NOT NULL ,

) ON [PRIMARY]
CREATE TABLE [dbo].[SaleLinePartIte m] (
[SLPItemID] [int] IDENTITY (1, 1) NOT NULL ,
[SLPSaleID] [int] NOT NULL ,
[SLPPartID] [int] NOT NULL ,
[SLPEquipmentIte mID] [int] NULL ,

) ON [PRIMARY]

These are partials (obviously). This is for a manufacturing company.
The business case is that sometimes a company wants to order the exact
same thing they ordered before. My problem, specifically, is when I
get to Inserting into the SaleLinePartIte m table, how do I get the NEW
SaleLineEquipIt emIDs? If there was just one, I could easily trap it
with a variable, but most of the time, sales include more than one
piece of equipment with different parts that make up that piece of
equipment.

Here's a stored procedure I did going from Quote to Sale. I'm not
even sure if it works, although it seems to in production - I'm just
not sure why. The immediate problem is copying a recordset with in
the same table.

CREATE PROCEDURE [dbo].[spInsertQuoteTo Sale] @intQuoteID int,
@intSaleID int OUTPUT
AS

INSERT INTO Sale (CustomerID, SaleDate, ShippingMethodI D, SaleTypeID,
TotalSaleAmount , JobName, DiscountAmount, FreightAmount, QuoteID,
InvoiceNumber, WONumber)
SELECT dbo.Quote.Custo merID, GETDATE(), dbo.Quote.Shipp ingMethodID,
dbo.Quote.Quote TypeID,
dbo.Quote.Total QuoteAmount, dbo.Quote.JobNa me,
dbo.Quote.Disco untAmount, dbo.Quote.Freig htAmount,
dbo.Quote.Quote ID, (Select InvoiceNumber from InvoiceNumber),
(Select WONumber from WorkOrderNumber ) From Quote Where QuoteID =
@intQuoteID

--DECLARE @intSaleID int
SET @intSaleID = @@IDENTITY

INSERT INTO SaleLineEquipme ntItem(SLSaleID , SLEquipmentID,
SLSerialNumber, SLEquipmentPric e, SLEQuantity, SLDescription)
SELECT @intSaleID, dbo.QuoteLineEq uipmentItem.QLE quipmentID,
dbo.QuoteLineEq uipmentItem.QLS erialNumber,
dbo.QuoteLineEq uipmentItem.QLE quipmentPrice,
dbo.QuoteLineEq uipmentItem.QLE Quantity,
dbo.QuoteLineEq uipmentItem.QLE Description
FROM QuoteLineEquipm entItem Where
dbo.QuoteLineEq uipmentItem.QLQ uoteID = @intQuoteID --order by
dbo.QuoteLineEq uipmentItem.QLE quipmentID

INSERT INTO SaleLinePartIte m(dbo.SaleLineP artItem.SLPSale ID,
dbo.SaleLinePar tItem.SLPPartID , SLPEquipmentIte mID,
dbo.SaleLinePar tItem.SLPStanda rd,
dbo.SaleLinePar tItem.SLPBackor der, dbo.SaleLinePar tItem.SLPUnitPr ice,
dbo.SaleLinePar tItem.SLPPartDe scription,
dbo.SaleLinePar tItem.SLPQuanti ty)
SELECT @intSaleID, dbo.QuoteLinePa rtItem.QLPPartI D,
QLPEquipmentIte mID,
dbo.QuoteLinePa rtItem.QLPStand ard,
dbo.QuoteLinePa rtItem.QLPBacko rder,
dbo.QuoteLinePa rtItem.QLPUnitP rice,
dbo.QuoteLinePa rtItem.QLPPartD escription,
dbo.QuoteLinePa rtItem.QLPQuant ity
FROM QuoteLinePartIt em WHERE dbo.QuoteLinePa rtItem.QLPQuote ID =
@intQuoteID --AND dbo.QuoteLinePa rtItem.QLPEquip mentItemID is null

UPDATE SaleLinePartIte m

SET SLPEquipmentIte mID = dbo.SaleLineEqu ipmentItem.Sale LineEquipItemID
FROM dbo.SaleLineEqu ipmentItem INNER JOIN
dbo.QuoteLineEq uipmentItem ON
dbo.SaleLineEqu ipmentItem.SLEq uipmentID =
dbo.QuoteLineEq uipmentItem.QLE quipmentID AND
dbo.SaleLineEqu ipmentItem.SLEQ uantity =
dbo.QuoteLineEq uipmentItem.QLE Quantity AND
dbo.SaleLineEqu ipmentItem.SLEq uipmentPrice =
dbo.QuoteLineEq uipmentItem.QLE quipmentPrice INNER JOIN
dbo.QuoteLinePa rtItem ON
dbo.QuoteLineEq uipmentItem.Quo teLineEquipItem ID =
dbo.QuoteLinePa rtItem.QLPEquip mentItemID INNER JOIN
dbo.SaleLinePar tItem ON
dbo.QuoteLinePa rtItem.QLPEquip mentItemID =
dbo.SaleLinePar tItem.SLPEquipm entItemID
WHERE (dbo.SaleLineEq uipmentItem.SLS aleID = @intSaleID) AND
(dbo.QuoteLineE quipmentItem.QL QuoteID = @intQuoteID)
GO

Sorry this is so long. Thanks for the help!!!
Maria

[Maria L]

Ignore the HUGE message I sent before. The SQL David sent was
PERFECT!!! I worked it through (which I should have done before
replying) and you had it.

I really appreciate it. As you can see if you look at my code, I was
making it too hard (I tend to do that).

Thanks again!!!
Maria

[Joe Celko]

I hope you know that IDENTITY can never be a key, that it has no check
digits so you cannot even validate it. You would never use it for
anything a customer has to see and use.

Can you explain the logical differences among "SaleID" (which, being
IDENTITY, is the physical location in storage of a row), a "SLSaleID"
and a "SLPSaleID" ? Why is "SaleLineEquipI temID" logically different
from "SLEquipmentID" ? Ditto with almost everything else you have posted.
Since they have different names, they **must** be logically different.
Surely, you would never, never name a data element for each table where
it located.

Naming data elements by the file they came from was how we had to locate
COBOL records. I hope that "SalesLine" does not refer to the PHYSICAL
sales form's printed lines on a piece of paper or a screen. The next
worst thing is prefixing the datatypes to data element names -- that is
BASIC programming.

If you had followed ISO-11179 standards for names, used DRI, and had
real keys, would the schema look more like this?

CREATE TABLE Sales
(sale_id INTEGER NOT NULL PRIMARY KEY
CHECK(<< validation code >>),
customer_id INTEGER NOT NULL,
sale_date DATETIME NOT NULL);

CREATE TABLE Equipment
(sale_id INTEGER NOT NULL REFERENCES Sales(sale_id),
equipment_id INTEGER NOT NULL,
PRIMARY KEY (sale_id, equipment_id));

CREATE TABLE Parts
(sale_id INTEGER NOT NULL,
equipment_id INTEGER NULL,
part_id INTEGER NOT NULL,
FOREIGN KEY (sale_id, equipment_id)
REFERENCES Equipment(sale_ id, equipment_id),
PRIMARY KEY (sale_id, equipment_id, part_id));

how do I get the NEW SaleLineEquipIt emIDs? <<

You don't have them in the first, if you know how to design an RDBMS,
instead of a sequential file system.

When get an order for equipment you have assembled before, you look up
the sales for that customer, you get the (sale_id, equipment_id), and
then the parts with that key. When you do the two insertions, you go to
the sales numbers table or routine and use it in both the Equipment and
Parts table.

Now this is a trival insertion procedure. 80% or more of the time that
you have complex DML, it is due to bad DDL. Bad DDL is usually attempts
to imitate a file system or an OO model in an RDBMS.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Maria L]

Joe -
Again thanks for your help. Although my naming conventions aren't
standard, we are on the same page with design. 1) The key values -
ID's are not used by the client or seen by the client. They are only
used for referential integrity and relational database management. 2)
I did name foreign keys different than their primary key
counterparts, depending on the table they were in. I know what it
means, and for now, that's all that matters because it's in
production. 3) There is a difference, in this business model, from
EquipmentID to SaleEquipmentID . While the SaleEquipmentID does
reference the original EquipmentID, it is stored seperately, because
the manufacturer wanted to be able to change each piece of equipment
as it was sold. Almost all the stuff they do is based on a
"standard", but things are added and subtracted all the time
(individual parts).

I believe the forms of RDMS are wonderful guidelines. But there are
business cases where the rules have to be bent a little to accomodate
business rules. While there is always more I can learn (being
primarily self-taught for the first 10 years of development - am now
an MCSD), I feel very confident in my relational database design
skills.

Thanks for pointing some stuff out. I do appreciate your help!
Maria