May 22, 2011 at 4:04 am
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 22, 2011 at 4:22 am
in the example quoted below by a fellow the insertion should not be done as ActivityIds 2, 4, 5, 6 already exists against the ActivityControllerID 4
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
May 22, 2011 at 5:32 am
Please do not cross-post. It fragments replies and takes longer to solve the issue.
No more replies here plz. Forward the discussions here : http://qa.sqlservercentral.com/Forums/Topic1112847-1292-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply