insert only if not exist

[oneeyeman1]

Hi, ALL,

Given the following definition:

CREATE TABLE verify(name varchar(50), condition text, error text);

CREATE UNIQUE INDEX ind_verify ON verify( name );

How do I make sure I insert the records only if the name is not present already, but if its present just do nothing and continue without error.

I want to populate this table every time I start the program and establish a good connection to the DB.

Is check subquery the only way?

If I do an insert right now I am getting an error about unique constraint violation.

Thank you.

Working with SAP ASE 16.1.

Also someone needs to add/fix the appropriate tag.

When I type "SAP ASE" I see: "SAP ASE - BW Enablement" SAP ASE - ERP Enablement", "SAP ASE - HADR Enablement" and "SAP HANA accelerator for SAP ASE". What I don't see is "Adaptive Server Enterprise".

Thx.

[Mark_A_Parsons]

re:tags ...

Instead of searching for 'ASE', try searching for 'Adaptive' (NOTE: this question has already been tagged with 'SAP Adaptive Server  Enterprise'.)

I have no idea why SAP has not provided a tag named 'SAP ASE'.

[oneeyeman1]

@MarkAParsons, yes I figured that much. TRIED "Adaptive" and it was there.

[Mark_A_Parsons]

There are a few ways to do what you want ...

We'll start by creating the table+index and performing an initial insert:

CREATE TABLE verify(name varchar(50), condition text, error text)
go
CREATE UNIQUE INDEX ind_verify ON verify( name )
go

insert verify (name, condition, error)
values ('name1', 'good condition', 'good error')
go

For each approach we'll perform two inserts:

1. one insert for the previous name (eg, 'name1') with 'bad condition' and 'bad error'; no insert should be performed
2. one for the next name (eg, 'name2') with 'good condition' and 'good error'; an insert should be performed

insert / select / where not exists(...)

-- should NOT insert

insert verify (name, condition, error)
select 'name1', 'bad condition', 'bad error'
where not exists(select name from verify where name = 'name1')
go

-- should insert

insert verify (name, condition, error)
select 'name2', 'good condition', 'good error'
where not exists(select name from verify where name = 'name2')
go

if not exists(...) / insert

-- should NOT insert

if not exists(select name from verify where name = 'name2')
begin
        insert verify (name, condition, error)
        values ('name2', 'bad condition', 'bad error')
end
go

-- should insert

if not exists(select name from verify where name = 'name3')
begin
        insert verify (name, condition, error)
        values ('name3', 'good condition', 'good error')
end
go

merge / when not matched

-- should NOT insert

merge into verify as tgt
      using (select 'name3','bad condition', 'bad error') as src (name,condition,error)
      on tgt.name = src.name
      when not matched
      then insert (name,condition,error)
           values (src.name, src.condition, src.error)
go

-- should insert

merge into verify as tgt
      using (select 'name4','good condition', 'good error') as src (name,condition,error)
      on tgt.name = src.name
      when not matched
      then insert (name,condition,error)
           values (src.name, src.condition, src.error)
go

At this point we should have 4 rows in our table, all with 'good condition' and 'good error' entries [NOTE: output has been edited to remove excessive spacing]:

select *
from verify
order by name
go

 name  condition      error
 ----- -------------- ----------
 name1 good condition good error
 name2 good condition good error
 name3 good condition good error
 name4 good condition good error

Obviously these example are basic T-SQL syntax.  I can't speak to how easy (or hard) these are to implement if using a programmed front end (eg, python, perl,), let alone how to handle any special characters that may be embedded in the values of the two text fields.

[tilman_model-bosch]

How about inserting into the table and check the return code of the INSERT command. If it is error 2601 just ignore and continue to insert more values ?

[oneeyeman1]

@@MarkAParsons,

 I think I found a better way.

I check the documentation and there is an "WITH IGNORE_DUP_KEY" option when creating an index.

So I just tested it and it works as I would expect. No subquery is needed to verify.

Thank you.