Inserting multiple rows into a table using data from 2 other tables

[wallaceoc80]

I have a table that is a relation table that is used in resolving a many-to-many relationship.

The table Group_App has columns (GroupID(pk), AppID(pk))

It resolves the relation ship between:

Group(GroupID(pk), GroupName)

and

Application(AppID(pk), AppName)

What I want to do is run a query that will fill the Group_App table with all the Applications from the Application table and for each AppID will have GroupID of 4.

So for example the Group_App table will have the following column pairings:

GroupID AppID
4 1
4 2
4 3
...

Can anybody tell me is it possible to write a query that will do this?

Thanks for the help!

Wallace

 

[skuhlman]

insert into Group_App (GroupID, AppID)
  select 4 as GroupID, AppID
    from Application
    where Application.AppID not in
              (select AppID from Group where GroupID=4)

 

[Andel]

You can use CROSS JOIN.

INSERT INTO Group_App (GroupID, AppID)
SELECT G.GroupID, A.AppID
FROM Group G CROSS JOIN Application A



Andel
andel@barroga.net

http://www.iluko.com