[bhuvaneswaran]

I have a stored procedure which has a insert statement.Before executing the insert query i am checking with "if not exists" condition for the availability of the record in the table. This is working fine, but occassionally duplicate(2 rows with same values for all columns) rows are inserted into that table. Can any one suggest what is the remedy??

FYI.. the table does not have any key constaints.

[mike_bike_kite]

1. set up a unique key on the table
2. make sure what you're selecting in the insert statement is unique ( see step 1 ) otherwise ...
3. insert ... select distinct ...

[bhuvaneswaran]

we cannot change the description of the table as this table is in production. The source table does always has only one record. Find the below sample code from the stored procedure which is inserting duplicate records
if not exists(select 1 from tablename where a = @value and b = null and c = null)
begin
insert into tablename() select a1,a2,a3 from tablename where...
end

[aflorin27]

I think you may be in the following situation:
1. user A evaluates the not exists() condition - returns true
2. user B evaluates the same not exists() condition - returns true
3. user A makes the insert statement
4. user B makes the same insert

I think you have to lock the table.

[mike_bike_kite]

Obviously it's difficult to debug whole system from 2 lines of code but …

I’m guessing that if this code gets called by multiple processes (users) at the same time then the following could happen – there is no data in the target table – then 2 parallel jobs could both test to see if no record exists in the target table – both would jobs would say there is no data – then both jobs would try to insert a rec – you end up with a duplicate.

You could change the code so it does the check and the insert in one statement ie :

insert target
select …
from source
where not exists( select 1 from target )

I personally would still create unique index on source and target tables – I know they should only ever contain one row and if that’s what they do hold then no part of the system should be affected. If suddenly either of the tables contains a duplicate then you’d get an error message immediately pointing you to the code with the problem.

As a last alternative you could just use select distinct when you read data from the target table.

MBK