Statement construction assistance - joins and inserts 2

[Haybails90]

Hey Folks,

I'm here to request some assistance in creating a statement.

I have two tables:

* groups_members -- a Cross Referencing table consisting of only two fields - GroupID (referencing a record in a 'groups' table) and EmpAutoID (referencing a record in an 'employees' table)
and
* employees (is a standard employee table with AutoID being the key of each employee record).

Here's what I need to do:

1.) I need a record created in the groups_members table for every employee in the employees table.
2.) This record in the groups_members table must have a GroupID of 181 and an EmpAutoID of the employees AutoID from the employees table.
3.) I need to make sure, of course, that I don't create a duplicate record within the groups_members table (for example, if a 181/employeeID record already exists in groups_members).
4.) Now, each employeeID may have multiple records in the groups_members table (referencing different GroupID numbers) but only one occurance of each EmpAutoID associated with a GroupID of 181).

Make sense?

I do my SQL statements in Query Analyzer. Feel free to ask for clarification if you need more information. Any and all assistance is greatly appreciated.



Haybails90

 

[SQLSister]

First create a unique index (or primary key) consisting of the two fields in your groups table. THat solves the duplication problem.

To get the initial group of 181 in the table, create an insert trigger on the employees table.

Questions about posting. See faq183-874

 

[Haybails90]

SQLSister,

Thank you for your speedy reply. Unfortunately <BIG BLUSH> you've lost me already.

I understand the need for an insert trigger on the employees table for newly created employee records (I'll get to that later today) ... but, for now, all I need is a statement which will allow me to create all the records in the groups_members table for all the currently existing records in the employees table. Any chance of you helping me create this statement?

Thank you again for your quick response!!!



Haybails90

 

[Haybails90]

SQLSister,

What I'm looking for is some sort of statement which does the following (in poorly written pseudo code):

1.) step through every record of the employees table.
2.) for each record in the employees table, grab the AutoID
3.) Look at the groups_members table and see if a record exists containing an assoaciation of this employees AutoID and a GroupID of 181. If it does, shout Hurrah and celebrate momentarily, if it doesn't, create a new record in the groups_members table which contains an EmpAutoID = to the AutoID from the employees table and a GroupID = 181.


Does that make sense?




Haybails90

 

[swampBoogie]

insert into group_members(groupId,empAutoId)
select 181,autoId from employees
where not exists
(select * from group_members
 where group_members.empAutoId = employees.autoId
   and group_members.groupId = 181)

 

[SQLSister]

swamp boogie's code should work. One thing I might point out to you is to stop thinking in terms of stepping through records and think in terms of how can I act on a group of records.

As far as the trigger, there are two psuedotables called in the inserted and deleted tables which are only avaliable in triggers. These contain the records that are being inserted or deleted (or in an update, both are used to show the old and new values). You will want to use the inserted table to identify the ids from the employees table to insert into the groups table.

You can look up the create trigger syntax in BOL (Books Online, SQL Server help), but the statement you want in the in the insert trigger is something like:

insert into group_members(groupId,empAutoId)
select 181,autoId from inserted

Note this code will only work in a trigger.


Questions about posting. See faq183-874

 

[Haybails90]

Swampboogie and SQLSister,

Thank you both for your assistance. This was/is exactly what I was/am looking for. In all honesty, the statement I was writing was headed in the exact direction of Swampboogies statement, I guess I was just a little 'gun shy' on actually running it. <G>

Thank you again, I'll give you both stars for your assistance.




Haybails90