Click here to Skip to main content
Rate this: bad
good
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 z421
Edited 27-Feb-11 0:25am
Keith Barrow146.5K
v2
Comments
Keith Barrow at 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 at 27-Feb-11 6:34am
   
Actually, the OP's command is valid with some other database engines such as MySQL.
_Ashish at 27-Feb-11 11:11am
   
MSSQL server doesn't support this way
mondo3 at 23-Sep-11 18:08pm
   
for number 2., I think you need to add "From tablename" right before the "Where"
mondo3 at 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')

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 6,775
1 OriginalGriff 6,696
2 CPallini 5,345
3 George Jonsson 3,599
4 Gihan Liyanage 2,751


Advertise | Privacy | Mobile
Web04 | 2.8.140922.1 | Last Updated 27 Feb 2011
Copyright © CodeProject, 1999-2014
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