473,385 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

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

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!!

Jul 20 '05 #1
6 2413
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:

INTEGER NOT NULL /* REFERENCES Sales (orderno) */, eq_partno INTEGER NOT

(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

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_partno) 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

Jul 20 '05 #2
>> 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
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?
Jul 20 '05 #3
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 ,

CREATE TABLE [dbo].[SaleLineEquipmentItem] (
[SaleLineEquipItemID] [int] IDENTITY (1, 1) NOT NULL ,
[SLSaleID] [int] NOT NULL ,
[SLEquipmentID] [int] NOT NULL ,

CREATE TABLE [dbo].[SaleLinePartItem] (
[SLPItemID] [int] IDENTITY (1, 1) NOT NULL ,
[SLPSaleID] [int] NOT NULL ,
[SLPPartID] [int] NOT NULL ,
[SLPEquipmentItemID] [int] NULL ,


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 SaleLinePartItem table, how do I get the NEW
SaleLineEquipItemIDs? 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

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].[spInsertQuoteToSale] @intQuoteID int,
@intSaleID int OUTPUT

INSERT INTO Sale (CustomerID, SaleDate, ShippingMethodID, SaleTypeID,
TotalSaleAmount, JobName, DiscountAmount, FreightAmount, QuoteID,
InvoiceNumber, WONumber)
SELECT dbo.Quote.CustomerID, GETDATE(), dbo.Quote.ShippingMethodID,
dbo.Quote.TotalQuoteAmount, dbo.Quote.JobName,
dbo.Quote.DiscountAmount, dbo.Quote.FreightAmount,
dbo.Quote.QuoteID, (Select InvoiceNumber from InvoiceNumber),
(Select WONumber from WorkOrderNumber) From Quote Where QuoteID =

--DECLARE @intSaleID int

INSERT INTO SaleLineEquipmentItem(SLSaleID, SLEquipmentID,
SLSerialNumber, SLEquipmentPrice, SLEQuantity, SLDescription)
SELECT @intSaleID, dbo.QuoteLineEquipmentItem.QLEquipmentID,
FROM QuoteLineEquipmentItem Where
dbo.QuoteLineEquipmentItem.QLQuoteID = @intQuoteID --order by

INSERT INTO SaleLinePartItem(dbo.SaleLinePartItem.SLPSaleID,
dbo.SaleLinePartItem.SLPPartID, SLPEquipmentItemID,
dbo.SaleLinePartItem.SLPBackorder, dbo.SaleLinePartItem.SLPUnitPrice,
SELECT @intSaleID, dbo.QuoteLinePartItem.QLPPartID,
FROM QuoteLinePartItem WHERE dbo.QuoteLinePartItem.QLPQuoteID =
@intQuoteID --AND dbo.QuoteLinePartItem.QLPEquipmentItemID is null

UPDATE SaleLinePartItem

SET SLPEquipmentItemID = dbo.SaleLineEquipmentItem.SaleLineEquipItemID
FROM dbo.SaleLineEquipmentItem INNER JOIN
dbo.QuoteLineEquipmentItem ON
dbo.SaleLineEquipmentItem.SLEquipmentID =
dbo.QuoteLineEquipmentItem.QLEquipmentID AND
dbo.SaleLineEquipmentItem.SLEQuantity =
dbo.QuoteLineEquipmentItem.QLEQuantity AND
dbo.SaleLineEquipmentItem.SLEquipmentPrice =
dbo.QuoteLineEquipmentItem.QLEquipmentPrice INNER JOIN
dbo.QuoteLinePartItem ON
dbo.QuoteLineEquipmentItem.QuoteLineEquipItemID =
dbo.QuoteLinePartItem.QLPEquipmentItemID INNER JOIN
dbo.SaleLinePartItem ON
dbo.QuoteLinePartItem.QLPEquipmentItemID =
WHERE (dbo.SaleLineEquipmentItem.SLSaleID = @intSaleID) AND
(dbo.QuoteLineEquipmentItem.QLQuoteID = @intQuoteID)

Sorry this is so long. Thanks for the help!!!
Jul 20 '05 #4
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!!!
Jul 20 '05 #5
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 "SaleLineEquipItemID" 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?

CHECK(<< validation code >>),
customer_id INTEGER NOT NULL,

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

equipment_id INTEGER 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 SaleLineEquipItemIDs? <<

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.

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!
Jul 20 '05 #6
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

Thanks for pointing some stuff out. I do appreciate your help!
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
by: Ray | last post by:
I have a data input form and need to automatically duplicate the existing record as a new record by clicking a button. The main purpose to duplicate the record is that the new record is very...
by: Cyberwolf | last post by:
OK, how to explain this. I have a table that will feed into another table using an append query. What I want to do is look at the table I am appending the record to to check for a duplicate...
by: 2D Rick | last post by:
Access2003 in XP If I open a form to a specific record and click on a command button with the following code I get a duplcate record: DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand...
by: planetthoughtful | last post by:
Hi All, I have a C# ASP.NET page that submits back to itself to insert details from a form into a database table. When / if the user refreshes the page (and gets the standard warning that POST...
by: Hexman | last post by:
Hello All, How do I catch duplicate record attempted to be added exception? As in: Catch ex As Exception Thanks, Hexman
by: ammie65 | last post by:
I have been trying to create a purchase order database in Access, and I have been successful in creating all the tables, queries and reports that I need. I have only one issue: I need to copy the...
by: Dave | last post by:
I really don't like the users getting an unhandled expception page, and I'm still to new with ASP.Net and C#. So please accept my appology for the 2 part question. SqlException (0x80131904) ...
by: teser3 | last post by:
I have my PHP inserting into Oracle 9i. But how do I prevent duplicate record entries? I only have 3 fields in the insert in the action page: CODE <?php $c=OCILogon("scott", "tiger",...
by: BenB | last post by:
Hello, A try: I have got an application which runs smoothly under Windows 2003 server with SQLserver 2005. No problems at all... The database is installed on the same server as the application....
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.