Avoid duplicates in INSERT INTO SELECT query in SQL Server

by

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.

More Related Contents:

  1. How do I perform an IF…THEN in an SQL SELECT?
  2. Inserting multiple rows in a single SQL query? [duplicate]
  3. Passing a varchar full of comma delimited values to a SQL Server IN function
  4. SQL update query using joins
  5. Is there a combination of “LIKE” and “IN” in SQL?
  6. How can I list all foreign keys referencing a given table in SQL Server?
  7. In SQL, how can you “group by” in ranges?
  8. T-SQL: Deleting all duplicate rows but keeping one [duplicate]
  9. Split function equivalent in T-SQL?
  10. Removing leading zeroes from a field in a SQL statement
  11. converting Epoch timestamp to sql server(human readable format)
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. How do I split a delimited string so I can access individual items?
  16. Common Table Expression, why semicolon?
  17. What’s the best way to select the minimum value from several columns?
  18. T-SQL split string based on delimiter
  19. Counting DISTINCT over multiple columns
  20. SQL Server SELECT into existing table
  21. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  22. Why use Select Top 100 Percent?
  23. How to check if a stored procedure exists before creating it
  24. Correct use of transactions in SQL Server
  25. How do I prevent a database trigger from recursing?
  26. What is the Null Character literal in TSQL?
  27. Aggregate function in an SQL update query?
  28. SQL Server : SUM() of multiple rows including where clauses
  29. Executing SQL Server Agent Job from a stored procedure and returning job result
  30. how does SELECT TOP works when no order by is specified?

Leave a Comment