1

Suppose I have the following tables:

----------------------------------------------------------------
| Products                                                     |
----------------------------------------------------------------
| id  | ContractId |  Name       | Price      | SpecialPriceId |
----------------------------------------------------------------
| 1   | 2002       | Apple      | 1.10       | 300             |
| 2   | 2002       | Banana     | 1.30       | 301             |
| 3   | 4500       | Orange     | 1.30       | 302             |
| 4   | 2002       | Strawberry | 1.20       | 303             |
----------------------------------------------------------------

-----------------------
| SpecialPrice        |
-----------------------
| id  | Price  | Vat  |
-----------------------
| 300 | 0.80   | 6.00 |
| 301 | 0.90   | 6.00 |
| 302 | 0.90   | 6.00 |
| 303 | 0.85   | 6.00 |
-----------------------

I need to make a query that copies all the records from the Products table where ContractId = 2002. Those copies should be inserted as new records in the same table.

But, there is a tricky part. For each new Product record, it should also create a copy of its correspondig SpecialPrice record and INSERT that copy as new record as well.

So the end result should be this:

----------------------------------------------------------------
| Products                                                     |
----------------------------------------------------------------
| id  | ContractId |  Name       | Price      | SpecialPriceId |
----------------------------------------------------------------
| 1   | 2002       | Apple      | 1.10       | 300             |
| 2   | 2002       | Banana     | 1.30       | 301             |
| 3   | 4500       | Orange     | 1.30       | 302             |
| 4   | 2002       | Strawberry | 1.20       | 303             |
| 5   | 2003       | Apple      | 1.10       | 304             |
| 6   | 2003       | Banana     | 1.30       | 305             |
| 7   | 2003       | Strawberry | 1.20       | 306             |
----------------------------------------------------------------

-----------------------
| SpecialPrice        |
-----------------------
| id  | Price  | Vat  |
-----------------------
| 300 | 0.80   | 6.00 |
| 301 | 0.90   | 6.00 |
| 302 | 0.90   | 6.00 |
| 303 | 0.85   | 6.00 |
| 304 | 0.80   | 6.00 |
| 305 | 0.90   | 6.00 |
| 306 | 0.85   | 6.00 |
-----------------------

What I can do is insert the copies of only the Products.

INSERT INTO [Products]
(ContractId, Name, Price, SpecialPriceId)
SELECT (2003, Name, Price, SpecialPriceId)
WHERE ContractId = 2002

But I'd need to do something like this:

INSERT INTO [Products]
(ContractId, Name, Price, SpecialPriceId)
SELECT (2003, Name, Price, ( SELECT 'old' [SpecialPrice] record, then INSERT new record in [SpecialPrice] and use new ID here ))
WHERE ContractId = 2002

How can I do that in a query? Or some Stored Procedure logic will help. I don't really have a clue how to make this work,

w00
  • 26,172
  • 30
  • 101
  • 147
  • Is/Are the ID column(s) `IDENTITY`? Do you expect parallel insert actions? With SQL Server 2012 you might use [`SEQUENCE`](https://msdn.microsoft.com/en-us/library/ff878091.aspx). – Shnugo Dec 07 '16 at 20:41
  • Something doesn't add up in your logic. Your first insert would create the rows in ProductId with ContractId = 2002, not 2003, so the result you are showing us are not the ones from the queries you suggested. Fix this and we can help better. – Guillaume CR Dec 07 '16 at 20:47
  • @GuillaumeCR You are right, my mistake. I fixed the INSERT example – w00 Dec 07 '16 at 20:48
  • @Shnugo The ID columns are `IDENTITY`.Parallel INSERT actions might occur. – w00 Dec 07 '16 at 20:50
  • One more question: for a single contract, are the product names unique? – Guillaume CR Dec 07 '16 at 20:54
  • You'll need a cursor. – shawnt00 Dec 07 '16 at 20:55
  • Use merge to insert to the specialprice table first then in the output clause you can either insert to products table directly or (if it doesn't meet the restrictions for this to be allowed directly) to a table variable that you can then insert to products. – Martin Smith Dec 07 '16 at 20:57
  • I was about to post a cursor approach using the cursor template from this page: https://msdn.microsoft.com/en-us/library/ms180169.aspx – shawnt00 Dec 07 '16 at 21:07
  • Here's the main body of the loop. The cursor grabs both ids and then iterates: `INSERT INTO Product (ContractId, Name, Price, SpecialPriceId) SELECT 2003, Name, Price, SpecialPriceId FROM Products WHERE id = @id;` – shawnt00 Dec 07 '16 at 21:08
  • `INSERT INTO SpecialPrice (Price, Vat) SELECT Price, Vat FROM SpecialPrice WHERE id = @spid;` – shawnt00 Dec 07 '16 at 21:08

0 Answers0