Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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.

Example: Add all users to all groups excluding admin

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.

What Do You Think?

Reader Comments

Commenting has been disabled.

Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub