Insert with a Select and an Exists

Question

Post reply

Insert with a Select and an Exists

Rog Saber

SSCrazy

Points: 2548
More actions
April 20, 2010 at 8:45 am

#219490

I have a table MAINT_REQ in which I want to insert values. I want 'mary' to have all of the same roles as 'jo'. 'jo' has a bunch of user roles in a table USERS_ROLES. The insert would look something like the following:
INSERT INTO MAINT_REQ (cust_no, user_id, db_role, request_datetime, request_type, complete_yorn)
SELECT cust_no, 'mary', db_role, getdate(), 'AROLE', 'N'
FROM USERS_ROLES WHERE user_id = 'jo';
However, if there is already an 'AROLE' row in MAINT_REQ, I don't want to add another one. I'm not sure exactly how to append this excluding condition in the select clause.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

It could be as simple as:

if not exists(select 1 from maint_req where request_type = 'AROLE')

insert maint_req..........

The probability of survival is inversely proportional to the angle of arrival.

WayneS SSC Guru Points: 95460 More actions · Answer 2

Rog Saber (4/20/2010)
I have a table MAINT_REQ in which I want to insert values. I want 'mary' to have all of the same roles as 'jo'. 'jo' has a bunch of user roles in a table USERS_ROLES. The insert would look something like the following:
INSERT INTO MAINT_REQ (cust_no, user_id, request_datetime, request_type, complete_yorn)
SELECT cust_no, 'mary', getdate(), 'AROLE', 'N'
FROM USERS_ROLES WHERE user_id = 'jo';
However, if there is already an 'AROLE' row in MAINT_REQ, I don't want to add another one. I'm not sure exactly how to append this excluding condition in the select clause.

You might want to look into using the EXCEPT operator in BOL - it's designed for just this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Rog Saber SSCrazy Points: 2548 More actions · Answer 3

Sorry, should have added that there can be multiple roles (db_role). I want all of jo's roles, just not the one that already exist in the MAINT_REQ table.

sturner SSC-Insane Points: 22411 More actions · Answer 4

Then EXCEPT or LEFT OUTER JOIN is what you want.

The probability of survival is inversely proportional to the angle of arrival.

WayneS SSC Guru Points: 95460 More actions · Answer 5

sturner (4/20/2010)
Then EXCEPT or LEFT OUTER JOIN is what you want.

Agreed.

EXCEPT will get you a list of all of those that you want them to have, minus the ones already existing.

LOJ will get you a list of all of those, with the ones already existing having a field being NOT NULL - just add the ones that are NULL for that field.

Both will be nice, fast set-based methods of accoplishing this.

If you need examples, please post some sample data and the expected output for that sample data has described in the first link in my signature below.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Rog Saber SSCrazy Points: 2548 More actions · Answer 6

I used the except. It works great - I have never used it before. Thank you.

Paul White SSC Guru Points: 150467 More actions · Answer 7

sturner (4/20/2010)
It could be as simple as:
if not exists(select 1 from maint_req where request_type = 'AROLE')
insert maint_req..........

Be very careful when using constructions like this, whether you use EXCEPT, EXISTS, or a JOIN.

See http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/12/t-sql-tuesday-002-patterns-that-do-not-work-as-expected.aspx

Be sure to read the comments to see how to modify the construction so it is guaranteed to work.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi