Insert multiple rows into composite primary key

[jonmccon]

I am trying to insert data from 2 tables (tblContacts and tblPermission)into another table (tblUserPermissions). The columns to which I need to insert the data in tblUserPermission (ContactID, and PermissionID) make up its primary key. Can anyone help me with this? Nothing I have tried has worked.

 

[jhall156]

How do you join the contacts and permissions tables? JHall

 

[tlbroadbent]

You'll need to write a query similar to the following. I can't give you the full statement because the JOIN criteria for the two existing tables is needed.

Insert tblUserPermission
Select
tblContacts.ContactID,
tblPermission.PermissionID
From tblContacts
Inner Join tblPermission
On tblContacts.<ColName>=tblPermission.<ColName>

If there is no JOIN criteria, are you planning to add a row to the new table for every combination of rows from the two existing tables? If so you can use a Cross Join. However, I can't believe that you would want to give every user every permission. You must have some criteria for matching the two tables or selecting records.

Cross Join example:
Insert tblUserPermission
Select
tblContacts.ContactID,
tblPermission.PermissionID
From tblContacts
Cross Join tblPermission Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[jonmccon]

Thanks yet again, Terry.