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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply