my dog learned polymorphism*
The moose likes JDBC and the fly likes Insert if not exist Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert if not exist" Watch "Insert if not exist" New topic
Author

Insert if not exist

Mark Miranda
Greenhorn

Joined: Feb 25, 2011
Posts: 24
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
Bartender

Joined: Oct 14, 2005
Posts: 18907
    
    8

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
Greenhorn

Joined: Feb 25, 2011
Posts: 24
Ok, thanks.
Mark Miranda
Greenhorn

Joined: Feb 25, 2011
Posts: 24
I found this example:

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



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

Joined: Oct 14, 2005
Posts: 18907
    
    8

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30944
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Insert if not exist