Insert of data in a query in SQL Server

Question

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.

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.

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

Please check your question and fix typos. There is no `(r,1)` in the example. On the other hand, there is `(x,2)` in the example, why you don't want to insert that combination? — Vladimir Baranov, Mar 10 '16 at 02:01

score 1 · Answer 1 · answered Mar 09 '16 at 20:21

1

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
)

answered Mar 09 '16 at 20:21

Tab Alleman

31,483
7
36
52

Even then I am getting duplicates from my temporary table – Deepti Mar 09 '16 at 20:26
If the table `ProductTemp` has duplicates in it, then you should de-dupe it prior to running this step. There are lots of examples of de-duping a table already on SO. – Tab Alleman Mar 09 '16 at 20:27
http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server – Tab Alleman Mar 09 '16 at 20:28

score 1 · Answer 2 · answered Mar 09 '16 at 21:36

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

For this you can use 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);

Abdul Rasheed · Accepted Answer · 2017-03-29T05:17:35.070

0

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

edited Mar 29 '17 at 05:17

answered Mar 10 '16 at 04:48

Abdul Rasheed

6,486
4
32
48

Insert of data in a query in SQL Server

3 Answers3