CODEWITHSUNDEEP
sqlsql-server
VolrathTheFallen
VolrathTheFallen

Reputation: 157

SQL - Copy multiple rows for each row from another query

I seem to have a relatively simple task to do, but my SQL knowledge is limited and my google fu is failing on me. Although I could work my way around it, by writing a powershell script or fooling around with notepad++, but I'm genuinly interested on how I can achieve this with SQL, especially if the tables become very big and a workaround isn't very feasable anymore.

Simplified, I have two tables:

Prices

Productname Customer_ID price1 price2 price3 price4
A 1 10 11 12 13
B 1 99 100 101 102
C 1 48 49 50 51
D 1 2 3 4 5
E 1 89 90 91 92

Customers

Name ID Customer_Group
B 2 ABC
C 3 ABC
D 4 ABC
E 5 ABC
F 6 ABC

Now I want to copy (i.e. insert) the 5 rows from the prices table where Customer_ID = 1 back into the prices table for each customer in the customers table where the Customer_Group = ABC. Every time the 5 rows are copied, I also need to set the field Customer_ID to the ID returned by the customers table query.

So the prices table looks like this:

Productname Customer_ID price1 price2 price3 price4
A 1 10 11 12 13
B 1 99 100 101 102
C 1 48 49 50 51
D 1 2 3 4 5
E 1 89 90 91 92
A 2 10 11 12 13
B 2 99 100 101 102
C 2 48 49 50 51
D 2 2 3 4 5
E 2 89 90 91 92
A 3 10 11 12 13
B 3 99 100 101 102
C 3 48 49 50 51
D 3 2 3 4 5
E 3 89 90 91 92

(and so on)

Upvotes: 1

Views: 1206

Answers (2)

Hogan
Hogan

Reputation: 70528

 SELECT *
 FROM CUSTOMERS            
 WHERE CUSTOMERS.NAME IN ('B','C','D','E','F') 
       -- may not be needed if you want all rows from customers table.

Will select all the customers you want to work with

Now we just join to the other table to get the data

 SELECT *
 FROM CUSTOMERS
 JOIN PRICES ON  PRICES.Customer_ID = 1
 WHERE CUSTOMERS.Customer_Group = 'ABC'

Select the columns you need

 SELECT PRICES.ProductName, 
        CUSTOMERS.CustomerID,
        PRICES.price1, PRICES.price2, PRICES.price3, PRICES.price4
 FROM CUSTOMERS
 JOIN PRICES ON  PRICES.Customer_ID = 1
 WHERE CUSTOMERS.Customer_Group = 'ABC'

If you want to insert that in a table add the insert statement at the start

 INSERT INTO PRICES 
      (Productname, Customer_ID, price1, price2, price3, price4, price5)
   SELECT PRICES.ProductName, 
          CUSTOMERS.CustomerID,
          PRICES.price1, PRICES.price2, PRICES.price3, PRICES.price4
   FROM CUSTOMERS
   JOIN PRICES ON  PRICES.Customer_ID = 1
   WHERE CUSTOMERS.Customer_Group = 'ABC'

Upvotes: 2

Thom A
Thom A

Reputation: 96044

At a total guess, this?

SELECT P.ProductName,
       P.Customer_ID,
       P.price1,
       P.price2, --This is a denormalised design, and you should fix this
       P.price3, --This is a denormalised design, and you should fix this
       P.price4 --This is a denormalised design, and you should fix this
FROM dbo.Prices P
--WHERE P.Customer = 1 --?
UNION ALL
SELECT P.ProductName,
       C.ID AS CustomerID,
       P.price1,
       P.price2, --This is a denormalised design, and you should fix this
       P.price3, --This is a denormalised design, and you should fix this
       P.price4 --This is a denormalised design, and you should fix this
FROM dbo.Prices P
     CROSS JOIN dbo.Customers C;
--WHERE P.Customer = 1 --?

Upvotes: 0

Related Questions