Insert distinct records from column in Table1 into column table 2 into row where they match on a different column

Question

The first table I want to move the Product.ProductName column into the second table called Inventory.ProductName but delete all the duplicate entries. This I can do, but when I insert these distinct values into Inventory.ProductName, it creates rows for them instead of associating them with the correct ProductID. Can't wrap my head around this one. Here's what I tried.

insert into dbo.Inventory (ProductName)
select distinct Product.ProductName
from dbo.Product
inner join dbo.Inventory on (Product.Name = Inventory.ProductID)

Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Tab Alleman, Jan 07 '16 at 19:31

AHiggins · Answer 1 · 2016-01-07T20:10:40.010

1

The rough syntax you are looking for is:

UPDATE dbo.Inventory 
SET ProductName = Product.ProductName
FROM 
    dbo.Inventory 
     INNER JOIN 
    dbo.Product ON 
        Inventory.ProductID = Product.Name

This will set the Product Name in Inventory equal to the Product Name in Product. Note that you will have an issue if multiple versions of Product.ProductName exist for a distinct Product.Name.

edited Jan 07 '16 at 20:10

answered Jan 07 '16 at 20:05

AHiggins

7,029
6
36
54

score 1 · Accepted Answer · answered Jan 07 '16 at 20:05

1

You should use Merge Statement:

MERGE Inventory AS target
USING (SELECT Name, ProductName FROM Product) AS source (ProductID, ProductName)
ON (target.ProductID = source.ProductID)
WHEN MATCHED
    THEN UPDATE SET Name = source.ProductName
WHEN NOT MATCHED 
    THEN INSERT (ProductID, Name)
VALUES (source.ProductID, source.ProductName);

You can find more here:https://msdn.microsoft.com/es-es/library/bb510625(v=sql.120).aspx

answered Jan 07 '16 at 20:05

georgeos

2,351
2
24
28

I liked this answer because I didn't know about MERGE, but I got this error `Cannot insert explicit value for identity column in table 'Inventory' when IDENTITY_INSERT is set to OFF.` I know you can turn it `ON` but I wondered if the query cannot be changed to avoid this error in the first place? – danny taki Jan 07 '16 at 20:26
Yeah, you can change the Insert statement, but the problem becomes when you need to relate to the Product table, because you´ll be using different ProductID. So, you should maintain the same ProductID for both tables in order to make a correct inner join. – georgeos Jan 07 '16 at 20:31
I see... the best approach should be create a new column for Inventory which will be the Identity, and you must use the same query I provided you inserting the ProductID, in order to maintain the relationship between tables. – georgeos Jan 07 '16 at 20:34
Trying to do this but I keep getting this error when I run the query `alter table dbo.Inventory add ID int Identity(1,1);` `Multiple identity columns specified for table 'Inventory'. Only one identity column per table is allowed` – danny taki Jan 07 '16 at 21:01
Have you removed the identity from ProductID? First remove it, then add the new column with the Identity. – georgeos Jan 07 '16 at 21:04
According to this answer I can't remove the Identity unless I drop the entire column. http://stackoverflow.com/questions/8230257/sql-server-remove-identity-from-a-column-in-a-table – danny taki Jan 07 '16 at 21:06
Oh... I haven't used SQL Server since a couple years ago. – georgeos Jan 07 '16 at 21:10

Insert distinct records from column in Table1 into column table 2 into row where they match on a different column

2 Answers2