How to prevent duplicate insert into SQL Server

Question

I have a table with million of records.

I have this query:

INSERT INTO t1 (recordA, recordB, inserttime) 
   SELECT recordA, recordB, GETDATE() 
   FROM t1 as l1 
   WHERE 
      l1.id_comp = @id_comp 
      AND l1.id_sq NOT IN (SELECT l2.id_sqa 
                           FROM leghe_formazioni as l2 
                           WHERE l2.id_comp = @id_comp)

And sometimes duplicate records and sometimes not.

The duplicate record have the same insert time.

Ironically, this question about duplicates itself appears to a be an SO duplicate of this post: http://stackoverflow.com/questions/17442901/how-to-avoid-duplicate-values-for-insert-in-sql — Tim Biegeleisen, Apr 14 '15 at 07:19
Presumably you've also got all the appropriate unique constraints in place too. — Jonathon Cowley-Thom, Apr 16 '15 at 08:45

score 2 · Answer 1 · answered Apr 14 '15 at 07:19

Check to see if the record already exists before doing the INSERT:

IF NOT EXISTS (SELECT recordA, recordB, GETDATE() 
FROM t1 as l1 
WHERE 
    l1.id_comp = @id_comp 
    AND l1.id_sq NOT IN (
        SELECT l2.id_sqa 
        FROM leghe_formazioni as l2 
        WHERE l2.id_comp=@id_comp
    ))
INSERT INTO t1 (recordA, recordB, inserttime) 
SELECT recordA, recordB, GETDATE() 
FROM t1 as l1 
WHERE 
    l1.id_comp = @id_comp 
    AND l1.id_sq NOT IN (
        SELECT l2.id_sqa 
        FROM leghe_formazioni as l2 
        WHERE l2.id_comp=@id_comp
    )

score 0 · Answer 2 · edited May 23 '17 at 11:43

Check those topics:

MySQL/PHP Inserting the same row twice

MYSQL avoid inserting same row twice

MySQL Rows Inserted Twice

Since you didn't really post much details about your problem Im afraid there is not much people can help.

You could always alter your query to maybe check if row exists before inserting it. Or try to select certain data, if result is null it means that there is no such data in DB.

score 0 · Answer 3 · answered Apr 14 '15 at 07:22

You can do this way in the sql:

INSERT INTO t1 (recordA, recordB, inserttime) 
SELECT recordA, recordB, GETDATE() 
FROM t1 as l1 
WHERE 
    l1.id_comp = @id_comp 
    AND l1.id_sq NOT IN (
        SELECT l2.id_sqa 
        FROM leghe_formazioni as l2 
        WHERE l2.id_comp=@id_comp
    )
    and not exists (
      select 1 from t1 
      where t1.recordA = li.recordA and t1.recordB = li.recordB)

How to prevent duplicate insert into SQL Server

3 Answers3