Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-06-27
3
Medium Priority
?
235 Views
Last Modified: 2012-05-07
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]

Open in new window

0
Comment
Question by:locdang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 24729886
Something like this perhaps:
CREATE PROCEDURE usp_CopyQuote
    @OldQuote CHAR(8),
    @NewQQQuote CHAR(8)
AS 
SET NOCOUNT ON
 
INSERT  dbo.IndQteOfferQty
		(
		  QQQuote,
		  QQLine,
		  QQQtyOff,
		  QQQteQty,
		  QQMerch,
		  QQFactPrc,
		  QQAccept,
		  QQAcceptDt,
		  [TIMESTAMP]
		)
SELECT  @NewQQQuote,
        QQLine,
        QQQtyOff,
        QQQteQty,
        QQMerch,
        QQFactPrc,
        QQAccept,
        QQAcceptDt,
        [TIMESTAMP]
FROM    dbo.IndQteOfferQty
WHERE   QQQuote = @OldQuote
 
INSERT  dbo.IndQteOffer
		(
		  QOQuote,
		  QOLine,
		  QOQtyOff,
		  QOOffer,
		  QOFreightMethod,
		  QOQteQty,
		  QOFreightQty,
		  QOMarkupPerc,
		  QOMUFix,
		  QOUPrice,
		  QOValue,
		  QOUCost,
		  QOMerch,
		  QOQC,
		  QODuty,
		  QODutyFix,
		  QOFreight,
		  QOFreightFix,
		  QOForexRate,
		  QOForexFix,
		  QORemark,
		  QOStkCode,
		  QOSalOrd,
		  QOSalOrdLn,
		  QOAccept,
		  QOAcceptDt,
		  QOShipDt,
		  [TimeStamp]
		)
SELECT  @NewQQQuote,
        QOLine,
        QOQtyOff,
        QOOffer,
        QOFreightMethod,
        QOQteQty,
        QOFreightQty,
        QOMarkupPerc,
        QOMUFix,
        QOUPrice,
        QOValue,
        QOUCost,
        QOMerch,
        QOQC,
        QODuty,
        QODutyFix,
        QOFreight,
        QOFreightFix,
        QOForexRate,
        QOForexFix,
        QORemark,
        QOStkCode,
        QOSalOrd,
        QOSalOrdLn,
        QOAccept,
        QOAcceptDt,
        QOShipDt,
        [TimeStamp]
FROM    dbo.IndQteOffer
WHERE   QQQuote = @OldQuote
 
INSERT  dbo.IndQteNotes
		(
		  QNQuote,
		  QNLine,
		  QNType,
		  QNNote,
		  QNPrtQte,
		  QNCopyOrd
		)
SELECT  @NewQQQuote,
        QNLine,
        QNType,
        QNNote,
        QNPrtQte,
        QNCopyOrd
FROM    dbo.IndQteNotes
WHERE   QQQuote = @OldQuote
 
INSERT  dbo.IndQteMaster
		(
		  QMQuote,
		  QMQteDesc,
		  QMType,
		  QMOrder,
		  QMDumOrder,
		  QMCust,
		  QMContact,
		  QMPhone,
		  QMFax,
		  QMCusEmail,
		  QMStatus,
		  QMPriority,
		  QMQryDt,
		  QMTenderDt,
		  QMExpiryDt,
		  QMReqShipDt,
		  QMCSO,
		  QMQteByCSO,
		  QMAccManager,
		  QMArtist,
		  QMQteReqNo,
		  QMNextDec,
		  QMNextLn,
		  QMNextNote,
		  QMFreightMethod,
		  QMArtSupplied,
		  QMOnChargeProd,
		  QMOnChargeMat,
		  QMOnChargeCour,
		  QMArtTimehh,
		  QMArtTimemin,
		  QMQteAccept,
		  QMRejReason,
		  QMRejComment,
		  QMQteRequest,
		  [Timestamp]
		)
SELECT  @NewQQQuote,
        QMQteDesc,
        QMType,
        QMOrder,
        QMDumOrder,
        QMCust,
        QMContact,
        QMPhone,
        QMFax,
        QMCusEmail,
        QMStatus,
        QMPriority,
        QMQryDt,
        QMTenderDt,
        QMExpiryDt,
        QMReqShipDt,
        QMCSO,
        QMQteByCSO,
        QMAccManager,
        QMArtist,
        QMQteReqNo,
        QMNextDec,
        QMNextLn,
        QMNextNote,
        QMFreightMethod,
        QMArtSupplied,
        QMOnChargeProd,
        QMOnChargeMat,
        QMOnChargeCour,
        QMArtTimehh,
        QMArtTimemin,
        QMQteAccept,
        QMRejReason,
        QMRejComment,
        QMQteRequest,
        [Timestamp]
FROM    dbo.IndQteMaster
WHERE   QQQuote = @OldQuote

Open in new window

0
 
LVL 1

Author Closing Comment

by:locdang
ID: 31597621
WOW! i was after advice, didn't expect it done! thanks so much!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24731568
I hope it does what you wanted. :)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

598 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