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

Question

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?

i want to use this statement in a vb.net-program. the string-literals 'value1' and 'value2' will be replaced at runtime. i a access-environment can i run this statement as a valid statement. and it works for me. — rushifell, Feb 02 '15 at 16:17
Then why your question is with SQL-SERVER tag? You can change this — Giorgi Nakeuri, Feb 02 '15 at 16:48

Giorgi Nakeuri · Answer 1 · 2015-02-02T16:14:02.840

0

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.

edited Feb 02 '15 at 16:14

answered Feb 02 '15 at 16:06

Giorgi Nakeuri

35,155
8
47
75

if i do NOT grouping, appends multiple records. that is not what i want. – rushifell Feb 02 '15 at 16:10
Then you asked incorrect question. You are selecting from table1 and inserting same values in Table1. There will be always dublicates in this scenario. Also you are grouping on values??? It is syntax error – Giorgi Nakeuri Feb 02 '15 at 16:13
the statement in my post works great without duplicates. – rushifell Feb 02 '15 at 16:18
Yes, but as i have said, you are getting all rows from table1 if there is no value1 and value2, then you are grouping and getting 1 record. Why? You can rewrite as my first example in my answer – Giorgi Nakeuri Feb 02 '15 at 16:29
Also this `GROUP BY 'value1', 'value2' ` will produce syntax error. – Giorgi Nakeuri Feb 02 '15 at 16:31
i copy and paste "if not exists(select * from table1 where field1='value1' and field2='value2') insert into table1 values('value1', 'value2')" to access. answer: not a valid statement or so... – rushifell Feb 02 '15 at 16:44

score 0 · Answer 2 · answered Feb 02 '15 at 16:15

;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

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

2 Answers2