May 20, 2011 at 11:09 pm
Hey all,
I want to check before inserting into a table if a specific set of values exists or not
I've two tables:
1- Activity
This table has two columns with the primary key as "AcitvityID"
e.g.
dbo.Activity
AcitivityID------------VAlue
1-----------------------45
2-----------------------89
3-----------------------64
4-----------------------94
5-----------------------89
6-----------------------62
7-----------------------43
1- ActivityAllocation
This table has three columns with the primary key as "ID" and the last two columns are foreign keys
e.g.
dbo.ActivityAllocation
ID------------ActivityControllerID------------ActivityID
1-----------------------4-----------------------2
2-----------------------4-----------------------4
3-----------------------4-----------------------5
4-----------------------4-----------------------6
5-----------------------8-----------------------1
6-----------------------8-----------------------3
5-----------------------8-----------------------4
6-----------------------8-----------------------5
5-----------------------8-----------------------6
6-----------------------8-----------------------7
I execute a query which returns some rows like
ActivityID
3
4
5
6
to insert in the AcitivityAllocation table but before inserting them into this table I want to check that in this table no such AcitivityControllerID exists which has exact same set of ActivityID. How to check that??
Regards
Kamran
May 21, 2011 at 2:36 am
You could use NOT EXISTS or a LEFT OUTER JOIN.
The requirement itself is a little unclear since you mention a AcitivityControllerID column that is not part of the result set you're trying to insert.
May 21, 2011 at 3:18 am
AcitivityControllerID can be any number suppose the following data needs to be inseted in ActivityAllocaiton table
ActivityID
3
4
5
6
ActivityControllerID------------ActivityID
11------------------------------3
11------------------------------4
11------------------------------5
11------------------------------6
Now see ActivityID 3, 4, 5, 6 already exists in the table ActivityAllocation but still it is a valid data as in the ActivityAllocaiton no single ActivityControllerID exists which as all the same ActivityID. I believe if I use NOT EXISTS or LEFT OUTER JOIN then all the ActivityIDs which are in the ActivityAllocation table will be ignored
May 21, 2011 at 3:55 am
Here's a short example using LEFT JOIN:
DECLARE @tbl TABLE
(
ID INT IDENTITY (1,1),
ActivityControllerID INT,
ActivityID INT
)
INSERT INTO @tbl
VALUES(4,2),
(4,4),
(4,5),
(4,6),
(8,1),
(8,3),
(8,4),
(8,5),
(8,6),
(8,7)
DECLARE @tbl2 TABLE
(
ActivityControllerID INT,
ActivityID INT
)
INSERT INTO @tbl2
VALUES(11,3),
(11,4),
(11,5),
(11,6)
INSERT INTO @tbl (ActivityControllerID,ActivityID)
SELECT t2.ActivityControllerID, t2.ActivityID
FROM @tbl2 t2
LEFT JOIN @tbl t1
ON t2.ActivityControllerID=t1.ActivityControllerID AND t2.ActivityID=t1.ActivityID
WHERE t1.ActivityID IS NULL
SELECT *
FROM @tbl
May 22, 2011 at 4:00 am
Thank you Bro for your interest in this issue and I really appreciate your efforts in this regard. But the problem still exists. If i modify example quoted by you by inserting the same set of values which already exists in the table ActivityAllocation even then the insertion is done successfully but according to scenario it should not be the case. Please see below:
DECLARE @tbl TABLE
(
ID INT IDENTITY (1,1),
ActivityControllerID INT,
ActivityID INT
)
INSERT INTO @tbl
VALUES(4,2),
(4,4),
(4,5),
(4,6),
(8,1),
(8,3),
(8,4),
(8,5),
(8,6),
(8,7)
DECLARE @tbl2 TABLE
(
ActivityControllerID INT,
ActivityID INT
)
INSERT INTO @tbl2
VALUES(11,2),
(11,4),
(11,5),
(11,6)
INSERT INTO @tbl (ActivityControllerID,ActivityID)
SELECT t2.ActivityControllerID, t2.ActivityID
FROM @tbl2 t2
LEFT JOIN @tbl t1
ON t2.ActivityControllerID=t1.ActivityControllerID AND t2.ActivityID=t1.ActivityID
WHERE t1.ActivityID IS NULL
SELECT *
FROM @tbl
Please see even the ActivityIds 2, 4, 5, 6 already exists against the ActivityControllerID 4 even then the records were inserted successfully
May 22, 2011 at 5:10 am
Oh, now I see what you're looking for...
Maybe the following code will do the trick:
;WITH cte AS
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ActivityControllerID ORDER BY t1.ActivityID) row
FROM @tbl2 t2
inner JOIN @tbl t1
ON t2.ActivityID=t1.ActivityID
)
--INSERT INTO @tbl (ActivityControllerID,ActivityID)
SELECT t2.ActivityControllerID, t2.ActivityID
FROM @tbl2 t2
LEFT JOIN @tbl t1
ON t2.ActivityControllerID=t1.ActivityControllerID AND t2.ActivityID=t1.ActivityID
WHERE NOT EXISTS (SELECT 1 FROM cte WHERE row = 4)
Please note that the check row = 4 implies there always will be four rows to be inserted.
May 22, 2011 at 6:05 am
Thank you Lutz for the reply. As I also want to run it on SQL Server 2005 then what changes are required in this code.
May 22, 2011 at 6:15 am
kamranzafar_ch (5/22/2011)
Thank you Lutz for the reply. As I also want to run it on SQL Server 2005 then what changes are required in this code.
CTE & ROW_NUMBER() were instroduced in SQL2005, so Lutz's code should work under SQL2005 without modification.
May 22, 2011 at 6:20 am
Thank you Lutz. It is working according to the expectations and it seems the problem in the original scenario will be resolved 😀
Thanks again and God Bless you and your family
Regards
Kamran
May 22, 2011 at 10:02 am
Just for the heck of it I'll throw in that you can use EXCEPT as well. But you must compare the same # of columns in both tables so it's not an obvious replacement for left join nor exists.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply