CTE with INSERT does the statement twice, sometimes

Question

I have a CTE that splits records, calculate days, generates id's, and a lot of other stuff on historicLicMed for reports, the requirement ask specifically to store the results of the CTE on a regular table. For that I adapted the CTE to have an INSERT into to this new table, the problem is that the insert gets executed twice for some reason on records that got previously split, which results in the new table storing about 3 times the amount of original records (from 30k to 80k, when the cte without the insert results in 50k-ish records).

Example data in the original table:

ID  IDHIST   INI      END      DD
--- ------- -------- -------- ---
1   x       20180101 20180205 36

What the cte itself does and what should be pasted into the new table:

ID  IDHIST   INI      END      DD
--- ------- -------- -------- --
1   x       20180101 20180131 31
2   x       20180202 20180205 5

What the cte with the insert does:

ID  IDHIST   INI      END      DD
--- ------- -------- -------- --
1   x       20180101 20180131 31
2   x       20180202 20180205 5
3   x       20180101 20180131 31
4   x       20180202 20180205 5

This is the entire cte, any help in the INSERT statement it's appreciated.

;WITH N(N) AS 
    (
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM SYS.ALL_COLUMNS
    ),
    D(N,F,T,MD,BP,EP,
    --COLUMNAS TABLA ORIGINAL HISTORICOLICMED
    VALIDO,IDHIST,ID_HR,TIPO,ID_TIPO,NOM_INC,RUT,NOMBRE,ID_EMP,NOM_POS,DIAS_DURAC,INI,FIN,NUM_LIC,ID_USU_ACT,ULT_ACT) AS  
    (
        SELECT n.N,d.INI,d.FIN,
            DATEDIFF(MONTH, d.INI, d.FIN),
            DATEADD(MONTH, n.N, DATEADD(DAY, 1-DAY(INI), INI)),
            DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.N, 
            DATEADD(DAY, 1-DAY(INI), INI)))),
            --COLUMNAS TABLA ORIGINAL
            D.VALIDO,D.IDHIST,D.ID_HR,D.TIPO,D.ID_TIPO,D.NOM_INC,D.RUT,D.NOMBRE,D.ID_EMP,D.NOM_POS,D.DIAS_DURAC,D.INI,D.FIN,D.NUM_LIC,D.ID_USU_ACT,D.ULT_ACT
        FROM N INNER JOIN HISTORICOLICMED AS D
        ON d.FIN >= DATEADD(MONTH, n.N-1, d.INI)
    ), E AS (
        SELECT VALIDO,IDHIST,ID_HR,TIPO,ID_TIPO,NOM_INC,RUT,NOMBRE,ID_EMP,NOM_POS,INI,FIN,NUM_LIC,ID_USU_ACT,ULT_ACT, --dias_durac
        --columnas originales INI / FIN
        CONVERT(NVARCHAR(10),f, 103) as original_INI, CONVERT(NVARCHAR(10),t, 103) as original_FIN, DIAS_DURAC,
        --columnas con ampliación
        CONVERT(NVARCHAR(10), CASE n WHEN 0 THEN f ELSE bp END,103) AS nuevo_INI,
        --new_INI = CASE n WHEN 0 THEN f ELSE bp END,
        CONVERT(NVARCHAR(10), CASE n WHEN md THEN t ELSE ep END,103) AS nuevo_FIN
        --new_INI = CASE n WHEN md THEN t ELSE bp END
        FROM D
        WHERE MD >= N
    ), I AS (
        SELECT E.*, 
                DATEDIFF(DD, NUEVO_INI,NUEVO_FIN)+1 as DIAS_DURAC2, 
                CONVERT(nvarchar(6),CAST(nuevo_ini as datetime),112) as PERPRO
        FROM E --ORDER BY IDHIST 
    )
    INSERT INTO DBO.HISTORICOLICMEDPROC 
    (VALIDO,IDHIST,ID_HR,TIPO,ID_TIPO,NOM_INC,RUT,NOMBRE,ID_EMP,NOM_POS,INI,FIN,NUM_LIC,ID_USU_ACT,ULT_ACT,ORIGINAL_INI,ORIGINAL_FIN,DIAS_DURAC,NUEVO_INI,NUEVO_FIN,DIAS_DURAC2,PERPRO)
    SELECT E.VALIDO,E.IDHIST,E.ID_HR,E.TIPO,E.ID_TIPO,E.NOM_INC,E.RUT,E.NOMBRE,E.ID_EMP,E.NOM_POS,E.INI,E.FIN,E.NUM_LIC,E.ID_USU_ACT,E.ULT_ACT,E.ORIGINAL_INI,E.ORIGINAL_FIN,E.DIAS_DURAC,E.NUEVO_INI,E.NUEVO_FIN,I.DIAS_DURAC2,I.PERPRO
    FROM E LEFT JOIN I ON E.IDHIST = I.IDHIST
GO

Answer 1

It was the join doing trouble, just called the last table and renamed the last 2 variables so I could call everithing in one go.

SELECT E.VALIDO,E.IDHIST,E.ID_HR,E.TIPO,E.ID_TIPO,E.NOM_INC,E.RUT,E.NOMBRE,E.ID_EMP,E.NOM_POS,E.INI,E.FIN,E.NUM_LIC,E.ID_USU_ACT,E.ULT_ACT,E.ORIGINAL_INI,E.ORIGINAL_FIN,E.DIAS_DURAC,E.NUEVO_INI,E.NUEVO_FIN,E.DIAS_DURAC2,E.PERPRO
    FROM I AS E