Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 INSERT INTO WHERE NOT EXISTS

Author  Topic 

mikecro
Starting Member

21 Posts

Posted - 2011-04-11 : 19:05:54
Hi,

I've been wrestling with this query in SqlServer 2005 and get the error message:

"Incorrect syntax near the keyword 'WHERE'."

Here's the query:

insert into Roles_Users (role_id, user_id)
values (@role_id, @user_id)
WHERE NOT EXISTS (SELECT user_id, role_id FROM Roles_Users WHERE user_id=@role_id AND role_id=@user_id)



I don't see a problem with it. Any ideas?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:08:41
What are you trying to do? Your query doesn't make sense since you aren't using two different tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mikecro
Starting Member

21 Posts

Posted - 2011-04-11 : 19:11:31
Insert a set of values in to the table if they don't already exist.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:15:32
Then why don't you just use INSERT INTO/VALUES and then handle the PK violation error if it does already exist? What's wrong with throwing the error?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:16:05
Or better yet, use TRY/CATCH.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mikecro
Starting Member

21 Posts

Posted - 2011-04-11 : 19:20:42
That would appear to be the easiest approach. Thanks for your help.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:48:38
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 21:20:25
>> I don't see a problem with it. Any ideas? <<

INSERT INTO does not have a WHERE clause.



--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

wided
Posting Yak Master

218 Posts

Posted - 2011-04-12 : 12:05:42
if NOT EXISTS (SELECT user_id, role_id FROM Roles_Users WHERE user_id=@role_id AND role_id=@user_id)
insert into Roles_Users (role_id, user_id)
values (@role_id, @user_id)


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-12 : 13:38:54
I would not recommend doing a check on the table just to see if it exists already. Let the constraint do its job and fire the duplicate message. Your application should gracefully handle this or use TRY/CATCH.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -