Insert if not exist

[Mark Miranda]

I have a table called EMPLOYEES with the columns (id,firstname,lastname)

How can I make sure to only insert when the entry doesn't exist?

currently my query statement is INSERT INTO EMPLOYEES VALUES ('kd123','Teemu','Selanne')

now this will definitely insert the entry regardless if it exist.

[Paul Clapham]

You could do a SELECT to look for the record first, and if you don't find then INSERT it.

Or you could put a key constraint in your database, so that trying to add the record a second time would throw an SQLException.

Or you could see if your database has some kind of an "INSERT-OR-UPDATE" statement: some do, I think, but I can't remember any details at the moment.

[Mark Miranda]

Ok, thanks.

[Mark Miranda]

I found this example:

What's 42, can you just put whatever number you want on there?

TOCID    PROP_ID     STR_VAL
1        1           Yes
1        2           No
2        1           Yes
2        2           No
3        2           Yes
4        1           Yes
4        2           No

INSERT INTO propval (tocid, prop_id, pos, str_val)


SELECT 42, 1, 0, 'No'
WHERE NOT EXISTS (SELECT * FROM propval WHERE prop_id = 1)

src: http://www.dbforums.com/microsoft-sql-server/1640697-insert-where-not-exists.html

[Paul Clapham]

If I understand that correctly, the four values in line 13 of your code block correspond to the four column names in line 10. So they should be the data you want to be in those columns in the row you're about to insert with that code.

[Jeanne Boyarsky]

Paul Clapham wrote:Or you could see if your database has some kind of an "INSERT-OR-UPDATE" statement: some do, I think, but I can't remember any details at the moment.

The merge statement sounds like what you are thinking of.