CREATE TABLE PolCoAseg (
    PolCoAsegID INT IDENTITY(1,1) PRIMARY KEY,
    EmpID SMALLINT NOT NULL,
    PolNumID DECIMAL(12) NOT NULL,
    ComID CHAR(5) NOT NULL,
    ComPar SMALLMONEY NOT NULL,
    ComParTip CHAR(1) NOT NULL,
    
    CONSTRAINT FK_PolCoAseg_Corredurias FOREIGN KEY (EmpID) REFERENCES Corredurias(EmpID),
    CONSTRAINT FK_PolCoAseg_Polizas FOREIGN KEY (EmpID,PolNumID) REFERENCES Polizas(EmpID,PolNumID),
    CONSTRAINT FK_PolCoAseg_Companias FOREIGN KEY (EmpID,ComID) REFERENCES Companias(EmpID,ComID)
);



INSERT INTO PolCoAseg (EmpId, PolNumId, ComID, ComPar, ComParTip)
SELECT p.EmpId, p.PolNumId, p.ComID2, p.ComPar2, p.ComPar2Tip
FROM Polizas p
INNER JOIN Companias c ON c.EmpID = p.EmpID AND c.ComID = p.ComID2
WHERE p.ComID2 IS NOT NULL AND p.ComID2 != ''
UNION
SELECT p.EmpId, p.PolNumId, p.ComID3, p.ComPar3, p.ComPar3Tip
FROM Polizas p
INNER JOIN Companias c ON c.EmpID = p.EmpID AND c.ComID = p.ComID3
WHERE p.ComID3 IS NOT NULL AND p.ComID3 != ''
UNION
SELECT p.EmpId, p.PolNumId, p.ComID4, p.ComPar4, p.ComPar4Tip
FROM Polizas p
INNER JOIN Companias c ON c.EmpID = p.EmpID AND c.ComID = p.ComID4
WHERE p.ComID4 IS NOT NULL AND p.ComID4 != ''
UNION
SELECT p.EmpId, p.PolNumId, p.ComID5, p.ComPar5, p.ComPar5Tip
FROM Polizas p
INNER JOIN Companias c ON c.EmpID = p.EmpID AND c.ComID = p.ComID5
WHERE p.ComID5 IS NOT NULL AND p.ComID5 != '';