Duplicate Quotation - What is the best approach for a stored proceedure to do this,

Hi everyone,

I need to make a stored procedure that looks at a quotation and then duplicates it.

A single 'Quotation' exists in four tables, one is a 'quote master table', one is 'quote notes table'  another is 'quote offers master table' and one is 'offer details'

Each table's entries are linked together through a common identifier (the quote number on the master record) and then another identifier for each entry (except in the master record table of course)

A couple ways I have thought of doing this is via ASP and SQL (asp looping and running a series of queries to insert what it finds only with the new quote number) but this would be ridiculously slow.

The other way would obviously be a stored procedure that you feed an existing quote number and a new quote number, then it perform all the logic necessary to do the job.

However I don't know how to 'for each' in sql for every record it finds, whilst I am sure there is a way I have been told that it the equivalent 'for each' method is a bad idea.

I have included a detailed description of the tabels in the code box (sorry that it is lengthy but i didn't want to leave anything out)

So how do I go about this? :D

Big thank you in advance.

Xavier.

-- This is the master table for the quote.
-- [QMQuote] is the quotation number.
 
 
[IndQteMaster](
	[QMQuote] [char](8) COLLATE Latin1_General_BIN NOT NULL,
	[QMQteDesc] [nvarchar](150) COLLATE Latin1_General_BIN NULL,
	[QMType] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMOrder] [char](6) COLLATE Latin1_General_BIN NULL,
	[QMDumOrder] [char](6) COLLATE Latin1_General_BIN NULL,
	[QMCust] [char](8) COLLATE Latin1_General_BIN NULL,
	[QMContact] [nvarchar](50) COLLATE Latin1_General_BIN NULL,
	[QMPhone] [nvarchar](50) COLLATE Latin1_General_BIN NULL,
	[QMFax] [nvarchar](50) COLLATE Latin1_General_BIN NULL,
	[QMCusEmail] [nvarchar](150) COLLATE Latin1_General_BIN NULL,
	[QMStatus] [char](2) COLLATE Latin1_General_BIN NULL,
	[QMPriority] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMQryDt] [datetime] NULL,
	[QMTenderDt] [datetime] NULL,
	[QMExpiryDt] [datetime] NULL,
	[QMReqShipDt] [datetime] NULL,
	[QMCSO] [char](100) COLLATE Latin1_General_BIN NULL,
	[QMQteByCSO] [char](100) COLLATE Latin1_General_BIN NULL,
	[QMAccManager] [nvarchar](100) COLLATE Latin1_General_BIN NULL,
	[QMArtist] [nvarchar](50) COLLATE Latin1_General_BIN NULL,
	[QMQteReqNo] [char](9) COLLATE Latin1_General_BIN NULL,
	[QMNextDec] [decimal](4, 0) NULL,
	[QMNextLn] [decimal](4, 0) NULL,
	[QMNextNote] [decimal](4, 0) NULL,
	[QMFreightMethod] [char](3) COLLATE Latin1_General_BIN NULL,
	[QMArtSupplied] [nvarchar](255) COLLATE Latin1_General_BIN NULL,
	[QMOnChargeProd] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMOnChargeMat] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMOnChargeCour] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMArtTimehh] [decimal](5, 0) NULL,
	[QMArtTimemin] [decimal](5, 0) NULL,
	[QMQteAccept] [char](1) COLLATE Latin1_General_BIN NULL,
	[QMRejReason] [char](3) COLLATE Latin1_General_BIN NULL,
	[QMRejComment] [nvarchar](255) COLLATE Latin1_General_BIN NULL,
	[QMQteRequest] [nvarchar](4000) COLLATE Latin1_General_BIN NULL,
	[Timestamp] [binary](8) NULL,
 CONSTRAINT [PK_IndQteMaster] PRIMARY KEY CLUSTERED 
(
	[QMQuote] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
 
 
 
 
 
 
 
-- this is the 'quote notes table' i mentioned, QOQuote is the quotation number,
-- the mapping is as follows:
-- [IndQteNotes].[QNQuote] = [IndQteMaster].[QMQuote]
 
[IndQteNotes](
	[QNQuote] [char](8) COLLATE Latin1_General_BIN NOT NULL,
	[QNLine] [decimal](5, 0) NOT NULL,
	[QNType] [char](3) COLLATE Latin1_General_BIN NULL,
	[QNNote] [nvarchar](4000) COLLATE Latin1_General_BIN NULL,
	[QNPrtQte] [char](1) COLLATE Latin1_General_BIN NULL,
	[QNCopyOrd] [char](1) COLLATE Latin1_General_BIN NULL,
 CONSTRAINT [PK_IndQteNotes] PRIMARY KEY CLUSTERED 
(
	[QNQuote] ASC,
	[QNLine] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
 
 
 
 
 
 
-- this is the 'offers master table' i mentioned, QOQuote is the quotation number,
-- the mapping is as follows:
-- [IndQteOffer].[QOQuote] = [IndQteMaster].[QMQuote]
 
 
[IndQteOffer](
	[QOQuote] [char](8) COLLATE Latin1_General_BIN NOT NULL,
	[QOLine] [decimal](4, 0) NOT NULL,
	[QOQtyOff] [decimal](4, 0) NOT NULL,
	[QOOffer] [decimal](2, 0) NOT NULL,
	[QOFreightMethod] [char](3) COLLATE Latin1_General_BIN NULL,
	[QOQteQty] [decimal](10, 3) NULL,
	[QOFreightQty] [decimal](10, 3) NULL,
	[QOMarkupPerc] [decimal](5, 2) NULL,
	[QOMUFix] [char](1) COLLATE Latin1_General_BIN NULL,
	[QOUPrice] [decimal](10, 5) NULL,
	[QOValue] [decimal](15, 2) NULL,
	[QOUCost] [decimal](10, 5) NULL,
	[QOMerch] [decimal](15, 5) NULL,
	[QOQC] [decimal](15, 5) NULL,
	[QODuty] [decimal](15, 5) NULL,
	[QODutyFix] [char](1) COLLATE Latin1_General_BIN NULL,
	[QOFreight] [decimal](15, 5) NULL,
	[QOFreightFix] [char](1) COLLATE Latin1_General_BIN NULL,
	[QOForexRate] [decimal](5, 2) NULL,
	[QOForexFix] [char](1) COLLATE Latin1_General_BIN NULL,
	[QORemark] [nvarchar](255) COLLATE Latin1_General_BIN NULL,
	[QOStkCode] [char](30) COLLATE Latin1_General_BIN NULL,
	[QOSalOrd] [char](6) COLLATE Latin1_General_BIN NULL,
	[QOSalOrdLn] [decimal](4, 0) NULL,
	[QOAccept] [char](1) COLLATE Latin1_General_BIN NULL,
	[QOAcceptDt] [datetime] NULL,
	[QOShipDt] [datetime] NULL,
	[TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_IndQteOffer] PRIMARY KEY CLUSTERED 
(
	[QOQuote] ASC,
	[QOLine] ASC,
	[QOQtyOff] ASC,
	[QOOffer] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
 
 
 
 
 
 
 
-- this is the 'offer details' i mentioned, QQQuote is the quotation number,
-- the mapping is as follows:
-- [IndQteOfferQty].[QQLine] = [IndQteOffer].[QOQuote] (which inturn = [IndQteMaster].[QMQuote])
-- [IndQteOfferQty].[QQLine] = [IndQteOffer].[QOLine]
-- [IndQteOfferQty].[QQQtyOff] = [IndQteOffer].[QOQtyOff]
 
 
[IndQteOfferQty](
	[QQQuote] [char](8) COLLATE Latin1_General_BIN NOT NULL,
	[QQLine] [decimal](4, 0) NOT NULL,
	[QQQtyOff] [decimal](4, 0) NOT NULL,
	[QQQteQty] [decimal](10, 3) NULL,
	[QQMerch] [money] NULL,
	[QQFactPrc] [char](1) COLLATE Latin1_General_BIN NULL,
	[QQAccept] [char](1) COLLATE Latin1_General_BIN NULL,
	[QQAcceptDt] [datetime] NULL,
	[TimeStamp] [binary](8) NULL,
 CONSTRAINT [PK_IndQteOfferQty] PRIMARY KEY CLUSTERED 
(
	[QQQuote] ASC,
	[QQLine] ASC,
	[QQQtyOff] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window