March 3, 2014 at 8:31 pm
Dear Data Wizards,
Please help a simpleton looking to learn SQL. In this case 2 users are missing from drop down menu of a field.
Just looking to add UserID 200 and/or 201 into Table2 by GroupID 100 if 200 and/or 201 is not present.
Here is what I have:
GO
IF Not Exist (SELECT TOP 1 1
FROM Table
WHERE UserID in (200,201)
AND GroupID = 100)
BEGIN
INSERT INTO Table2 (UserID, GroupID)
Values (200,100);
INSERT INTO Table2 (UserID, GroupID)
Values (201,100);
END
Thank you for your all your help in advance.
V
March 4, 2014 at 12:20 am
Your statement won't work 100%, because if only one is missing, nothing is inserted at all.
Try this:
WITH CTE_InsertValues AS
(
SELECT UserID = 200, GroupID = 100
UNION ALL
SELECT UserID = 201, GroupID = 100
)
INSERT INTO Table(UserID, GroupID)
SELECT UserID, GroupID
FROM CTE_InsertValues c
LEFT JOIN Table t ON c.UserID = t.UserID and c.GroupID = t.GroupID
WHERE t.UserID IS NULL;
The join will check if a row is already in the destination table or not.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2014 at 12:37 am
Silly me .. I was trying with MERGE... It was so simple with CTE. Thank you @Koen Verbeeck
March 4, 2014 at 7:59 am
SQLCJ (3/4/2014)
Silly me .. I was trying with MERGE... It was so simple with CTE. Thank you @Koen Verbeeck
MERGE would be useful if you wanted to update a value if it existed and insert if it didn't.
March 4, 2014 at 8:25 am
Thanks everyone for your help. But for curiosity sake. How would it look with a MERGE statement?
March 4, 2014 at 9:02 am
the.roof (3/4/2014)
Thanks everyone for your help. But for curiosity sake. How would it look with a MERGE statement?
Like this. I borrowed the CTE from Koen.
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;
CREATE TABLE #Test (UserID INT, GroupID INT, FOUND BIT);
WITH CTE_InsertValues AS
(
SELECT UserID = 200, GroupID = 100
UNION ALL
SELECT UserID = 201, GroupID = 100
)
MERGE #test AS [Target]
USING (SELECT UserID, GroupID FROM CTE_InsertValues) AS [source]
ON ([target].UserID = [source].UserID and [target].GroupID = [source].GroupID)
WHEN MATCHED THEN
UPDATE SET [target].found = 1
WHEN NOT MATCHED THEN
INSERT (UserID, GroupID, found)
VALUES ([source].UserID, [source].GroupID, 0);
SELECT * FROM #test
I added an extra column otherwise the update is pointless. You can test the UPDATE part by running everything after the CREATE TABLE.
March 7, 2014 at 11:44 am
Thank you everyone for your insight and help. Hope everyone has a great weekend.:-)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy