May 6, 2019 at 4:43 am
Hello,
I would like to use either a Unique Constrain or Check Constrain to determine if an insert should be executed or if an update should be executed....here is the pseudo code of scenario:
I have a table named Foo with the following columns: User_Name, Visit_Number
The UserName column captures the user name, and the column Visit_Number contains the number of time the user has logged into the site. If the UserName 'Roger@Duff.com' never visited the site then I would like to perform the following :
--Create a Unique Constrain on column UserName
CREATE UNIQUE CLUSTERED INDEX IDX_UserNameUnique ON dbo.Foo (UserName);
SET @UserName = 'Roger@Duff.com'
IF IDX_UserNameUnique = True
BEGIN
Insert into Foo Values UserName = 'Roger@Duff.com', Visit_Number = 1
END
Else IF IDX_UserNameUnique = False
BEGIN
Update Foo Set Visit_Number = Visit_Number + 1 WHERE UserName = @UserName
END
Is the above doable in SQL 2017?
Sorry if I doubled spam you, the original post never made it to the forum.
Cheers,
Raf
May 6, 2019 at 8:26 am
You cant use a constraint or index to determine your flow.
This article will help you with the insert/update logic
May 8, 2019 at 11:03 am
I suppose you could achieve this using TRY-CATCH. Try to Insert it. If the insert fails on a violation of that constraint, increment the visit count for the user.
I personally would not implement this methodology.
May 8, 2019 at 2:57 pm
Using IF EXISTS would be a way to go about this.
IF EXISTS(SELECT UserName FROM Foo WHERE UserName = @UserName)
INSERT...
ELSE
UPDATE...
May 8, 2019 at 5:16 pm
I would follow Chris M's strategy. Why check for the constraint? Look for the data.
May 8, 2019 at 7:25 pm
The normal way to do with is by performing an UPDATE followed by an INSERT if no rows were updated, this is sometimes referred to as an upsert:
BEGIN TRANSACTION;
--
DECLARE @RowCount int
--
UPDATE dbo.Foo
SET Visit_Number = Visit_Number + 1
WHERE UserName = @UserName
--
SET @RowCount = @@ROWCOUNT
--
IF @RowCount = 0 BEGIN
Insert into Foo (UserName, Visit_Number)
Values (@UserName ,1)
END
--
COMMIT TRANSACTION;
If it's a particularly large table an index on Foo(UserName) might help performance.
May 12, 2019 at 4:27 am
Thank you for the suggestions!
The premise behind my logic is that the constraint check is already identifying the existence of the condition.
My current the approach is using the IF EXIST(Select... , but, even with a properly formed index is still too slow.
I am going to try the Upsert suggestion from Jonathan, and will report back (thank you Jonathan!)
Hi Schleep - I was also thinking the approach of leveraging TRY-CATCH, along the same lines per your suggestion. Besides the possible performance hit while leveraging the this approach, can you please share why would you not implement this method?
Regards,
Raf
May 13, 2019 at 11:11 am
Well, you asked specifically how it could be achieved using constraint violations, so that's what I came up with.
IMHO, it's clumsy at best. And, you must hard code either the name of the constraint or the or the error number.
I'd always do IF EXISTS. Upsert (which I also think clumsy) can work, but beware of triggers, which could affect @@ROWCOUNT.
Cheers
May 14, 2019 at 6:44 am
You can also do an upsert using merge:
merge #data as target
using (select @username) as source(username)
on target.username = source.username
when matched then update set visits = visits + 1
when not matched then insert(username,visits) values (source.username,1);
May 15, 2019 at 1:14 am
My current the approach is using the IF EXIST(Select... , but, even with a properly formed index is still too slow. I am going to try the Upsert suggestion from Jonathan, and will report back (thank you Jonathan!)
Devil in details.
How exactly have implemented the approach? Can you post the actual code you've tried?
UPSERT does effectively the same thing as 2 consecutive statements UPDATE-INSERT. So, it won't make any difference.
You better look at the execution plan and find what's holding up the queries.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply