SQL Server insert if not exists best practice [EXTENDED]

Question

INSERT `locality` (province_id)
SELECT DISTINCT `province_id`
FROM `locality` localreturn
WHERE
   NOT EXISTS (SELECT * FROM `locality` local
              WHERE localreturn.province_id = local.province_id)

If the query tested passes how do i insert a value? a normal insert would be like:

INSERT INTO `locality` (`province_id`) VALUES (1919)

Leeched Source: SQL Server insert if not exists best practice

Your query doesn't make any sense. Basically you are trying to insert id's into a table where ids not present in that same table!! zero rows will be inserted with the first query — Pரதீப், Oct 26 '14 at 16:45

score 0 · Answer 1 · 2014-10-26T16:56:16.147

0

try

  BEGIN
    IF NOT EXISTS(SELECT * FROM `locality` local WHERE  local.province_id=@id) 
    BEGIN
     INSERT `locality` (province_id) values(@id)
    END
  END

edited Oct 26 '14 at 16:56

answered Oct 26 '14 at 16:47

This answer is already present in the link given in the question!! Give some description about your answer that will help others. In the question he used only table(locality). – Pரதீப் Oct 26 '14 at 16:51

SQL Server insert if not exists best practice [EXTENDED]

1 Answers1