Avoid duplicates in INSERT INTO SELECT query in SQL Server

Question

I have the following two tables   Table1 ---------- ID   Name 1    A 2    B 3    C  Table2 ---------- ID   Name 1    Z   I need to insert data from Table1 to Table2  I can use the following syntax   INSERT INTO Table2 Id  Name  SELECT Id  Name FROM Table1   However  in my case  duplicate IDs might exist in Table2  in my case  it s just  1   and I don t want to copy that again as that would throw an error    I can write something like this   IF NOT EXISTS SELECT 1 FROM Table2 WHERE Id 1  INSERT INTO Table2  Id  name  SELECT Id  name FROM Table1  ELSE INSERT INTO Table2  Id  name  SELECT Id  name FROM Table1 WHERE Table1 Id lt  gt 1   Is there a better way to do this without using IF - ELSE  I want to avoid two INSERT INTO-SELECT statements based on some condition

User · Answer

In MySQL you can do this   INSERT IGNORE INTO Table2 Id  Name  SELECT Id  Name FROM Table1   Does SQL Server have anything similar

User · Answer

In my case  I had duplicate IDs in the source table  so none of the proposals worked  I don t care about performance  it s just done once  To solve this I took the records one by one with a cursor to ignore the duplicates  So here s the code example  DECLARE  c1 AS VARCHAR 12   DECLARE  c2 AS VARCHAR 250   DECLARE  c3 AS VARCHAR 250     DECLARE MY cursor CURSOR STATIC FOR Select c1  c2  c3 from T2 where        OPEN MY cursor FETCH NEXT FROM MY cursor INTO  c1   c2   c3  WHILE   FETCH STATUS   0 BEGIN     if  select count 1           from T1         where a1    c1         and a2    c2             0              INSERT INTO T1             values   c1   c2   c3       FETCH NEXT FROM MY cursor INTO  c1   c2   c3 END CLOSE MY cursor DEALLOCATE MY cursor

User · Answer

Using ignore Duplicates on the unique index as suggested by IanC here was my solution for a similar issue  creating the index with the Option WITH  IGNORE DUP KEY  In backward compatible syntax   WITH IGNORE DUP KEY is equivalent to WITH IGNORE DUP KEY   ON    Ref   index option

User · Answer

I just had a similar problem  the DISTINCT keyword works magic   INSERT INTO Table2 Id  Name  SELECT DISTINCT Id  Name FROM Table1

User · Answer

I was facing the same problem recently    Heres what worked for me in MS SQL server 2017    The primary key should be set on ID in table 2    The columns and column properties should be the same of course between both tables  This will work the first time you run the below script  The duplicate ID in table 1  will not insert     If you run it the second time  you will get a      Violation of PRIMARY KEY constraint error   This is the code   Insert into Table 2 Select distinct   from Table 1 where table 1 ID  gt 1

User · Answer

A simple DELETE before the INSERT would suffice   DELETE FROM Table2 WHERE Id    SELECT Id FROM Table1  INSERT INTO Table2  Id  name  SELECT Id  name FROM Table1   Switching Table1 for Table2 depending on which table s Id and name pairing you want to preserve

User · Answer

A little off topic  but if you want to migrate the data to a new table  and the possible duplicates are in the original table  and the column possibly duplicated is not an id  a GROUP BY will do   INSERT INTO TABLE 2  name    SELECT t1 name   FROM TABLE 1 t1   GROUP BY t1 name

User · Answer

Using NOT EXISTS   INSERT INTO TABLE 2    id  name  SELECT t1 id         t1 name   FROM TABLE 1 t1  WHERE NOT EXISTS SELECT id                     FROM TABLE 2 t2                    WHERE t2 id   t1 id    Using NOT IN   INSERT INTO TABLE 2    id  name  SELECT t1 id         t1 name   FROM TABLE 1 t1  WHERE t1 id NOT IN  SELECT id                        FROM TABLE 2    Using LEFT JOIN IS NULL   INSERT INTO TABLE 2    id  name     SELECT t1 id            t1 name      FROM TABLE 1 t1 LEFT JOIN TABLE 2 t2 ON t2 id   t1 id     WHERE t2 id IS NULL   Of the three options  the LEFT JOIN IS NULL is less efficient   See this link for more details

User · Answer

From SQL Server you can set a Unique key index on the table for  Columns that needs to be unique

[sql] Avoid duplicates in INSERT INTO SELECT query in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-insert