Click here to Skip to main content
12,252,860 members (64,031 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Hi all
I want to insert a new record into my table if does not exist.
When I write this code for example:

insert into tablename (code) values ('1448523')
WHERE not exists(select * from tablename where code='1448523')

I get an error

Incorrect syntax near the keyword WHERE

When I use if not exists again I have an error.
How do I to insert new record in my table if not exists?
Posted 26-Feb-11 20:37pm
ely z431
Edited 27-Feb-11 0:25am
Keith Barrow157.1K
v2
Comments
Keith Barrow 27-Feb-11 6:25am
   
I put your sql in pre tags to format it better, and fixed some minor grammar problems too.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

see here[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Yout Sql command is Incorrect , Insert Command doesn't have Where clause.

Where Clause is applicable to Update, Select and Delete Commands

insert into tablename (code) values ('1448523')
WHERE not exists(select * from tablename where code='1448523') --incorrect in insert command

you have two ways:

1.
If Not Exists(select * from tablename where code='1448523')
Begin
insert into tablename (code) values ('1448523')
End

2.
insert into tablename (code)
 Select '1448523' Where not exists(select * from tablename where code='1448523')
  Permalink  
Comments
Keith Barrow 27-Feb-11 6:34am
   
Actually, the OP's command is valid with some other database engines such as MySQL.
_Ashish 27-Feb-11 11:11am
   
MSSQL server doesn't support this way
mondo3 23-Sep-11 18:08pm
   
for number 2., I think you need to add "From tablename" right before the "Where"
mondo3 23-Sep-11 18:09pm
   
so it should be:
insert into tablename (code)
Select '1448523' From tablename Where not exists(select * from tablename where code='1448523')
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

I was reading through some old threads, and stumbled upon this one. A really good blog article which benchmarks various ways of accomplishing this task can be found here.[^]

They use a few techniques: "Insert Where Not Exists", "Merge" statement, "Insert Except", and your typical "left join" to see which way is the fastest to accomplish this task. It's a good read for those who are looking for speed!
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160426.1 | Last Updated 23 Jul 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100