July 23, 2014 at 5:12 am
hi,
I have this
UPDATE EPSBankRef
SET OrthoID = '@Request.inpHidden~'
WHERE BankID = SUBSTRING('@Request.TransDesc~', 0, 25)
I wish to add, if there isn't a match for the WHERE clause then INSERT the OrthoID and SUBSTRING('@Request.TransDesc~', 0, 25) s a new row.
not sure how to do it tho, thanks
July 23, 2014 at 5:22 am
There are two ways you could do this. The first is using the MERGE keyword. I have not used MERGE, though, because it hasn't yet been useful to me. So you would have to look it up in Books Online.
The second solution would be an IF...ELSE statement. Something like:
IF (SELECT BankID FROM EPSBankRef
WHERE BankID = SUBSTRING('@Request.TransDesc~', 0, 25) ) IS NOT NULL
BEGIN
UPDATE EPSBankRef
SET OrthoID = '@Request.inpHidden~'
WHERE BankID = SUBSTRING('@Request.TransDesc~', 0, 25);
END
ELSE
INSERT INTO EPSBankRef (BankID, OrthoID)
VALUES (BankID, OrthoID);
GO
You could also use a SELECT in place of the VALUES if you're pulling the data from another table. And obviously you need to fix the column list for both sides of the INSERT to reflect the true columns you're inserting into.
Hope that helps.
July 23, 2014 at 10:53 am
I'd recommended just using MERGE. It isn't that hard to work with. Something like this:
MERGE EPSBankRef AS Target
USING ( VALUES
(
@OrthoID,
@BankID
) ) AS SOURCE (OrthoID, BankID)
ON TARGET.BankID = SUBSTRING(@BankID, 0, 25)
WHEN MATCHED THEN
UPDATE SET
OrthoID = Source.OrthoID
WHEN NOT MATCHED THEN
INSERT
(OrthoID, BankID)
VALUES (
Source.OrthoID,
Source.BankID
);
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 23, 2014 at 10:57 am
Like Jack I would consider the merge statement. One caveat is to be careful around filtered indexes.
Here is more info on that bug http://sqlblog.com/blogs/paul_white/archive/2012/12/09/merge-bug-with-filtered-indexes.aspx.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply