Sometimes when you are working with databases it is faster to add everything and remove selected data than it is to just add in what you need. For example I recently needed to create a new many-to-many table which contained all possible combinations of the two parent tables excluding 3 cases.
Take this contrived example. Say we have a users table and a groups table with PK's 'userId' and 'groupId' respectively and we want to form a many-to-many table named userGroups with all possible combinations of userId and groupId excluding the admin groupId of 1.
You will note the use of the NOT EXISTS sub query to exclude any records that may already exist in the userGroups table preventing any PK violations. While not strictly necessary if you are starting with an empty table it can be useful if you already have some data in there.
INSERT INTO dbo.userGroups (
userId
,groupId
)
SELECT DISTINCT
u1.userId
,g1.groupId
FROM
dbo.users AS u1
,dbo.groups AS g1
WHERE
NOT EXISTS (
SELECT *
FROM dbo.userGroups AS ug
WHERE ug.userId = u1.userId
AND ug.groupId = g1.groupId
)
AND g1.groupId <> 1
Now I know this is a contrived example, but what it demonstrates is the key point that you can populate any table with data combined from two or more other tables.