0

I have a table where it holds some duplicate entries, I would like to copy over the distinct entries to another table with out looping the data. I need to check if the distinct data exists in other table and insert what ever is missing. Here is the query I am writing, I feel like it can be implement better

CREATE TABLE ForgeRock
([productName] varchar(13));

INSERT INTO ForgeRock
([productName])
 VALUES
   ('OpenIDM'), ('OpenAM'), ('OpenDJ'), ('OpenDJ'),('OpenDJ1');

CREATE TABLE ForgeRock1
([productName] varchar(13));

 DECLARE @prodName NVARCHAR(MAX)
 SELECT DISTINCT @prodName = STUFF((SELECT ',' + productName
 FROM ForgeRock
 FOR XML PATH('')) ,1,1,'')

 set @prodName = ''''+replace(@prodName,',',''',''')+''''

 INSERT INTO ForgeRock1 (productName)
 SELECT DISTINCT productName FROM ForgeRock WHERE
 productName NOT IN (SELECT productName FROM ForgeRock1
 where productName NOT IN (@prodName))

Here is the sample fiddle I tried out http://sqlfiddle.com/#!18/9dbe8f/1/0, is this query efficient or can it be better

Developer
  • 8,390
  • 41
  • 129
  • 238
  • Does this answer your question? [How to simply delete duplicate records in SQL Server?](https://stackoverflow.com/questions/56437256/how-to-simply-delete-duplicate-records-in-sql-server) – Stu Aug 17 '21 at 07:56
  • I don't want to delete I need to copy over distinct data to other table and check if the entry already exists. If entry exists don't do anything else insert the data – Developer Aug 17 '21 at 08:00
  • It appears you thought that `@prodName` would work as a list: you are completely mistaken. A variable or parameter is simply data, in this case a single string, so it's the same as `WHERE productName <> @prodName` – Charlieface Aug 17 '21 at 09:12

1 Answers1

1

This query should do what you want :)

INSERT INTO ForgeRock1 (productName)
SELECT DISTINCT productName FROM ForgeRock fr
WHERE NOT EXISTS ( SELECT 1 FROM ForgeRock1 fr1 WHERE fr1.productName = fr.productName )
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69