Copy rows from one table to another, ignoring duplicates

by

Your problem is that you need another where clause in the subquery that identifies what makes a duplicate:

INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
                 FROM destTable 
                 WHERE (srcTable.Field1=destTable.Field1 and
                       SrcTable.Field2=DestTable.Field2...etc.)
                 )

As noted by another answerer, an outer join is probably a more concise approach. My above example was just an attempt to explain using your current query to be more understandible. Either approach could technically work.

INSERT INTO destTable
SELECT s.field1,s.field2,s.field3,... 
FROM srcTable s 
       LEFT JOIN destTable d ON (d.Key1 = s.Key1 AND d.Key2 = s.Key2 AND...)
WHERE d.Key1 IS NULL

Both of the above approaches assume you are woried about inserting rows from source that might already be in destination. If you are instead concerned about the possibility that source has duplicate rows you should try something like.

INSERT INTO destTable
SELECT Distinct field1,field2,field3,... 
FROM srcTable

One more thing. I’d also suggest listing the specific fields on your insert statement instead of using SELECT *.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to return only the Date from a SQL Server DateTime datatype
  3. How to calculate age (in years) based on Date of Birth and getDate()
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Referring to a Column Alias in a WHERE Clause
  6. Function vs. Stored Procedure in SQL Server
  7. Limit on the WHERE col IN (…) condition
  8. converting Epoch timestamp to sql server(human readable format)
  9. Getting result of dynamic SQL into a variable for sql-server
  10. How to get difference between two rows for a column field?
  11. What’s the best way to select the minimum value from several columns?
  12. Counting DISTINCT over multiple columns
  13. SQL Server SELECT into existing table
  14. Why use Select Top 100 Percent?
  15. How to check if a stored procedure exists before creating it
  16. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  17. varbinary to string on SQL Server
  18. Scope of temporary tables in SQL Server
  19. SQL Select Upcoming Birthdays
  20. Select a Column in SQL not in Group By
  21. SQL Server 2005 ROW_NUMBER() without ORDER BY
  22. Can an INNER JOIN offer better performance than EXISTS
  23. Find the real column name of an alias used in a view?
  24. How to get the identity of an inserted row?
  25. How to create a pivot query in sql server without aggregate function
  26. TSQL – How to use GO inside of a BEGIN .. END block?
  27. SQL Server FOR XML Path make repeating nodes
  28. What does collation mean?
  29. Convert bigint to datetime
  30. Replacing NULL and empty string within Select statement

Leave a Comment