Insert Statement

I'm trying to create a stored procedure that inserts "UserID" and "ReportID" into the "UserReports" table. IF the UserID and ReportID combination already exists then don't insert it.

Something like:
If not Exists @UserID +@ReportID then
 (
    Insert @UserID into the UserID column,
          @ReportID into the ReportID column,
)
else end

use UserReports table.
mattkovoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
You can try like this:
IF NOT EXISTS (SELECT 1 FROM UserReports WHERE UserID = @UserID AND ReportID = @ReportID)
BEGIN
INSERT INTO UserReports(UserID, ReportID) VALUES(@UserID, @ReportID)
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROC dbo.urProcName
@UserID INT,
@ReportID INT
AS
BEGIN
      SET NOCOUNT ON
      INSERT INTO UserReports(UserID, ReportID)
      SELECT @UserID , @ReportID
      WHERE NOT EXISTS (SELECT 1 FROM  UserReports WHERE UserID=@UserID  AND ReportID = @ReportID)
END      
0
Kevin CrossChief Technology OfficerCommented:
Agree with Aneesh's suggestion as well.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Kevin CrossChief Technology OfficerCommented:
And using Aneesh's structure for procedure if you didn't already have that, here is the way using IF, but again Aneesh's works fine.
CREATE PROC dbo.urProcName 
@UserID INT, 
@ReportID INT 
AS 
BEGIN
      SET NOCOUNT ON;
 
      IF NOT EXISTS (SELECT 1 FROM UserReports WHERE UserID = @UserID AND ReportID = @ReportID)
          INSERT INTO UserReports(UserID, ReportID) VALUES(@UserID, @ReportID);
END   

Open in new window

0
BrandonGalderisiCommented:
Using "if not exist insert select" is less reliable than using "insert where not exists" because the first will execute as two statements and the second will execute as one.  I have run into problems where the record does not exists when the "if not exists" runs for two processes at exactly the same time but then the inserts both run.  This is especially a problem in high usage systems where lots of concurrent write operations are happening.

That is why I would use http:#a24816783
0
mattkovoAuthor Commented:
Thanks guys!  I was originally going with mwvisa1 but decied to go with aneeshattingal:'s solution because of what BrandonGalderisi's statement. So I split up the points for everyone.  Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.