CODEWITHSUNDEEP
ms-access-2002
rushifell
rushifell

Reputation: 11

mssql-statement to append a record into a table with check for duplicates

I need a query for adding a data record to a table. I would like to check in advance whether the record, which is to be inserted into the table, already exists. Here is my code (it works):

INSERT INTO table1(field1, field2) 
    SELECT 'value1', 'value2' 
    FROM   table1 
    WHERE  NOT EXISTS (SELECT * 
                       FROM   table1 
                       WHERE  field1 = 'value1' 
                          AND field2 = 'value2') 
    GROUP  BY 'value1', 'value2'

I believe that my code is not very effective. Perhaps there is a better statement in order to achieve the same result?

Upvotes: 0

Views: 39

Answers (2)

Xedni
Xedni

Reputation: 4695

;with src as
(
    select distinct
        field1 = 'value1',
        field2 = 'value2'
    from table1
)
insert into Table1(Field1,Field1)
select s.Field1, s.Field2
from src s
left outer join table1 d --dest
    on s.Field1 = d.Field1
        and s.Field2 = d.Field2
where d.Field1 is null

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

First of all you dont't need grouping here at all

if not exists(select * from table1 where field1='value1' and field2='value2')
insert into table1 values('value1', 'value2')

Second you can add unique index to those 2 columns, and SQL will do that job instead of you:

CREATE UNIQUE NONCLUSTERED INDEX [IX_table1] ON [dbo].[table1]
(
    [field1] ASC,
    [field2] ASC
)

In both cases I would recommend adding index on those 2 columns.

Upvotes: 0

Related Questions