CODEWITHSUNDEEP
sql-serverdatabase-design
Pawel
Pawel

Reputation: 309

Insert data from one table to another in one column while inserting new data in another column

I am currently learning T-SQL and I made this table that I use to learn sql and database design. At the moment I have data in one table that looks something like this:

ProductID     AmountOfTypeA    AmountOfTypeB    AmountOfTypeC    some other atributes 
                                                                describing product....
---------     -------------    -------------    -------------
101           1                2                0
102           0                1                3
103           5                0                0

etc...

I want to normalize this data and eliminate the redundant 0 where there is no products of that type. I made a new table with columns: 'ProductID', 'Type' and 'Amount' and use the Foreign key to link the two tables by ProductID. At the moment I am going column by column using INSERT INTO dbo.tProdTypes (ProductID, Amount) FROM dbo.tProducts and then I manually populate the 'Type' column.

However I was wondering is it possible to do it in one query, specifically to insert some data from other table as one column and some custom data as second column. Also I don't mind going column by column but if there is a way to insert all columns at once it would be great.

Upvotes: 1

Views: 48

Answers (2)

gofr1
gofr1

Reputation: 15977

Also you can use UNPIVOT:

;WITH cte AS (
SELECT *
FROM (VALUES
(101, 1, 2, 0),
(102, 0, 1, 3),
(103, 5, 0, 0)
) as t (ProductID, AmountOfTypeA, AmountOfTypeB, AmountOfTypeC)
)

SELECT ProductID, Amount, [Type] 
FROM 
   (SELECT ProductID, AmountOfTypeA AS A, AmountOfTypeB AS B, AmountOfTypeC AS C
   FROM cte) p
UNPIVOT
   (Amount FOR [Type] IN 
      (A, B, C)
)AS unpvt;

Output:

ProductID   Amount      Type
----------- ----------- ----------- 
101         1           A
101         2           B
101         0           C
102         0           A
102         1           B
102         3           C
103         5           A
103         0           B
103         0           C

(9 row(s) affected)

Upvotes: 1

Sherman
Sherman

Reputation: 853

I think something like this would work:

INSERT tProdTypes (ProductID, Amount, Type)
SELECT ProductID, AmountOfTypeA, 'TypeA'
FROM tProducts
WHERE AmountOfTypeA > 0
UNION
SELECT ProductID, AmountOfTypeB, 'TypeB'
FROM tProducts
WHERE AmountOfTypeB > 0
UNION
SELECT ProductID, AmountOfTypeC, 'TypeC'
FROM tProducts
WHERE AmountOfTypeC > 0

Upvotes: 2

Related Questions