ForEach record insert in sql server

I have t开发者_运维问答hree sql tables tblDocumentType (Doctypeid), tblDiscipline (DisciplineID) tblDocType_Discipline

For each Doctypeid in tblDocumentType I need to insert all DisciplineID and Doctypeid in to tblDocType_Discipline table.

What's the best way to write a sql query?

Insert into tblDocType_Discipline
(DocTypeId,DisiplineId)
SELECT  dt.DocTypeID, d.DisciplineId
FROM dbo.tblDocumentType dt
FULL OUTER JOIN dbo.tblDiscipline d 
ON 1 = 1

I think you are looking for a full outer join.

You need to have a relationship between Doctypeid and DisciplineID to get the results you want.

Something like this, provided the aforementioned:

insert into tblDocType_Discipline
select Doctypeid
from tblDocumentType
where DisciplineID in
(
    select DisciplineID
    from tblDisicpline
)

That query assumes that your tblDocumentType table has a field for DisciplineID. If it doesn't, there is no way to create that relationship unless you have a separate join table.