在SQL服务器的查询中插入数据

I have a table Product:

我有一个桌子产品:

Name       Description
----------------------
  x            1
  y            2
  z            3

I have another table Producttemp:

我有另一个桌子产品:

Name   Description
------------------
  x         1
  x         1
  x         2
  r         3
  r         3
  z         8
  z         8

I need to insert data from Producttemp into Product and only that data which is in combination of Name and Description.

我需要将Producttemp的数据插入到产品中，只需要将名称和描述结合在一起的数据。

So as of now x,1 should not be inserted because this combination already exists in Product table and only (r,1) and (z,8) should be inserted and we don't have to insert the duplicate combinations.

所以从现在开始，x 1不应该被插入因为这个组合已经存在于Product表中只有(r,1)和(z,8)应该被插入我们不需要插入重复的组合。

I am trying with this query :

我正在尝试这个问题:

create table #vhf (pk_id numeric)

INSERT INTO product (product_name, product_description)
OUTPUT INSERTED.* INTO #vhf
    SELECT  
        temp.product_name,
        temp.product_description
    FROM 
        producttemp
    WHERE 
        NOT EXISTS (SELECT distinct temp.product_name FROM product prj, product temp 
                    WHERE temp.product_description = prj.product_description 
                      AND temp.product_name = prj.product_name)

This query is returning all the values that are not there in product table but it is also inserting the duplicate rows

这个查询返回product表中不存在的所有值，但它也插入重复的行

3 个解决方案

#1

Try this one

试试这个

INSERT INTO [Product]
SELECT  DISTINCT PT.[Name],PT.[Description]
FROM    [Producttemp] AS PT
LEFT OUTER JOIN [Product] AS P ON P.[Name] = PT.[Name] 
       AND P.[Description] = PT.[Description]
WHERE   P.[Name] IS NULL

#2

Your attempt to de-dupe is completely wrong.

你试图去欺骗是完全错误的。

Do it like this:

这样做:

...
SELECT  pt.product_name,
        pt.product_description
FROM producttemp pt
where  NOT EXISTS (SELECT * FROM product prj
 where pt.product_description=prj.product_description 
 and pt.product_name=prj.product_name
)

#3

From what I understood you do not want to insert rows from 'Producttemp' that are already in 'Product'.

根据我的理解，您不希望插入'Producttemp'中已经在'Product'中的行。

For this you can use MERGE

为此，您可以使用MERGE

MERGE Product AS P
USING(SELECT DISTINCT Name,Descrip FROM Producttemp) AS PT
ON  P.Name = PT.Name AND P.Descrip=PT.Descrip
WHEN NOT MATCHED THEN
 INSERT(Name,Descrip) VALUES(PT.Name,PT.Descrip);

#1