CODEWITHSUNDEEP
sql-servert-sql
Adam Mills
Adam Mills

Reputation: 85

How to duplicate a record set spread over multiple tables in MSSQL?

I have a database which holds Quotations and Product Configurations, there is one header table for a Quotation under which there may be one or more Configurations. Within a configuration there are tables which define products and hardware in shades of one to one and one to many. If a customer wishes to alter any aspect of a quotation the current quotation must be frozen and another produced with a different quotation number. The quotation number is an auto ident field in the header table.

I wrote a long winded cloning script that worked at the start but as additional columns were required the cloning script quickly got out of date. This leaves sales support with the job of having to clone the quotation by hand which can often lead to mistakes.

How would you approach this in such a manner as to easily define the relationships, propogate the new id whilst not having to explicity specify all the columns in each table?

Upvotes: 2

Views: 166

Answers (1)

GilM
GilM

Reputation: 3761

You can 

SELECT * 
INTO #tableN
FROM tableN
WHERE QuotationID = @OldQuotationID

into temp tables for each of the tables involved, so that you don't have to know the exact schema of each table. Then, after you've created the new header row and captured the new QuotationId value, you can update these tables with the new QuotationID and insert them back into the tables they came from.

Upvotes: 2

Related Questions