Solved

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

Posted on 2009-06-27
3
225 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
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 32
How to SQL Trace a SPECIFIC query 24 59
SQL Server Data Tools for Visual Studio 2015 - backward compatibility. 2 89
testing sql16 on win10 vs OS16 2 36
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now