[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1321
  • Last Modified:

MSSQL TSQL - Insert where not exists

Hi,

I've trint to write some SQL to insert into a MSSQL database via asp - I trying to prevent duplicate rows being inserted into the database -  but the following example keeps failing for some reason?

Insert into ProdFeatures (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14) Values ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14') Where Not Exists (select * from ProdFeatures where f1 = '1' and f12 = '12')

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'Where'.

As always, any help is greatly appreciated.

Many thanks

JamWales
0
Jamie
Asked:
Jamie
1 Solution
 
cyberkiwiCommented:
Change to select form, not values

Insert into ProdFeatures (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14)
select '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14'
Where Not Exists
(select * from ProdFeatures where f1 = '1' and f12 = '12')
0
 
TonyRebaCommented:
I suggest you to troubleshoot your select statement and see if that returns the data you need..
sql = select * from ProdFeatures where f1 = '1' and f12 = '12'

0
 
Rajesh_mjCommented:
Try this one.......

If not exists (select * from ProdFeatures where f1 = '1' and f12 = '12')
begin
Insert into ProdFeatures (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14) Values ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14')
end
0
 
JamieAuthor Commented:
Hi cyberkiwi:

Many thanks for your solution - works perfectly - I did try using select from sys.dual but this didn't work as it was an oracle command!  

Thanks to everyone else for your suggestions, but this was the best solution for my particular case.

Regards

Jamie
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now