CODEWITHSUNDEEP
sqlsql-serverinsertsql-insert
Richard F.
Richard F.

Reputation: 67

SQL Server: Insert from second table for every unique value in first table with matching condition

I have a first table of customer data:

|BillTo |ShipTo_Code|Name    |Address           |ProductNum |UoM |Price  |
|:------|-----------|--------|------------------|-----------|----|-------|
|101308 |0526       |Store 1 |118 Washington St.|           |    |       |
|101308 |0542       |Store 2 |62 Central St.    |           |    |       |
|101308 |0716       |Store 3 |15 Walnut Rd.     |           |    |       |
|101308 |0817       |Store 4 |211 Sudbury Rd.   |           |    |       |

And I have a second table that has product and pricing data for customers:

|BillTo |ProductNum |UoM |Price  |
|:------|-----------|----|-------|
|101308 |3100121    |CS  |2.90   |
|101308 |3100697    |CS  |2.45   |
|101308 |3300072    |CS  |67.80  |
|101308 |3300075    |CS  |5.45   |

I need to insert the pricing data into the customer table so that all 4 products appear for every change in ShipTo_Code. It should end up like this:

|BillTo |ShipTo_Code|Name    |Address           |ProductNum |UoM |Price  |
|:------|-----------|--------|------------------|-----------|----|-------|
|101308 |0526       |Store 1 |118 Washington St.|3100121    |CS  |2.90   |
|101308 |0526       |Store 1 |118 Washington St.|3100697    |CS  |2.45   |
|101308 |0526       |Store 1 |118 Washington St.|3300072    |CS  |67.80  |
|101308 |0526       |Store 1 |118 Washington St.|3300075    |CS  |5.45   |
|101308 |0542       |Store 2 |62 Central St.    |3100121    |CS  |2.90   |
|101308 |0542       |Store 2 |62 Central St.    |3100697    |CS  |2.45   |
|101308 |0542       |Store 2 |62 Central St.    |3300072    |CS  |67.80  |
|101308 |0542       |Store 2 |62 Central St.    |3300075    |CS  |5.45   |
|101308 |0716       |Store 3 |15 Walnut Rd.     |3100121    |CS  |2.90   |
|101308 |0716       |Store 3 |15 Walnut Rd.     |3100697    |CS  |2.45   |
|101308 |0716       |Store 3 |15 Walnut Rd.     |3300072    |CS  |67.80  |
|101308 |0716       |Store 3 |15 Walnut Rd.     |3300075    |CS  |5.45   |
etc.

The only match between the 2 tables is the BillTo.

I've spent a lot of time looking at similar questions here, but have not really been able to apply the solutions I've seen in threads so far.

This thread gave me a good start, but it's doing it within the same table.

Any assistance would be very much appreciated.

Upvotes: 1

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This seems like a join:

select c.*, st.*
from customers c join
     secondtable st
     on c.billto = st.billto
order by c.shipto_code, st.productnum;

Upvotes: 1

Related Questions