hansolo hansolo - 4 months ago 7
SQL Question

MS SQL SERVER Insert New Column Values for every row of existing table

I have an existing table of customers. I want to add a NEW row for each customer based on ALL of the services our company offers.

AS IS:

Customer - ID
Freddy - 123


TO BE:

Customer - ID - Service
Freddy - 123 - Serv1
Freddy - 123 - Serv2
Freddy - 123 - Serv3


etc ...

Right now I have been working with below but it has not given desired output:

INSERT INTO Customers (Service) VALUES
('Serv1'), ('Serv2'), ('Serv3')

Answer

You need some sort of JOIN, in this case a CROSS JOIN:

SELECT c.Customer, c.Id, v.s
FROM Customers c CROSS JOIN
     (VALUES ('Serv1'), ('Serv2'), ('Serv3')) v(s);

However, it doesn't make sense to insert this into the original table. That table only has two columns, but you seem to want a third one.