INSERT INTO EXCEPT doesn´t work

[Elite Hunter]

I have this statement:

INSERT INTO LlamadasRecompra (CodigoCompra, ClaveVive, Paciente, Status, Tel1, Tel2,
 Cel, Presentacion, Dosis, Compras, Medico,
 FechaUC, FechaCargaReceta, Receta)
(SELECT COD_COMPRA AS CodigoCompra, CLAVE_V AS ClaveVive, PACIENTE AS Paciente, STATUS AS Status, TEL1 AS Tel1, TEL2 AS Tel2,
 CEL AS Cel, PRESENTACION_UC AS Presentacion, DOSIS_DIARIA_VIVE AS Dosis, COMPRAS AS Compras, MEDICO AS Medico,
 CONVERT(datetime, FECHA_UC, 103) AS FechaUC, CONVERT(datetime, FECHA_CARGA_RECETA, 103) AS FechaCargaReceta, RECETA AS Receta 
FROM InfoParticipantes 
EXCEPT SELECT CodigoCompra, ClaveVive, Paciente, Status, Tel1, Tel2,
 Cel, Presentacion, Dosis, Compras, Medico,
 FechaUC, FechaCargaReceta, Receta FROM LlamadasRecompra)

When I run the SELECT-EXCEPT SELECT part it runs perfectly returning more than 3000 registers, but when I run it all it inserts nothing to my table. Any idea why?

Thanks for your help.

[ck9663]

Are there any error? Try to isolate the issue. Do an SELECT...INTO.. . then check the output table. If it's still happening, use CTE for your select then insert it to your target table.


Happy Coding!!!


~~ CK

[Elite Hunter]

Thanks for your help CK. The code doesn't shows any error, it just doesn't insert any row. As I said before, if I run the SELECT-EXCEPT SELECT part alone it returns a table with all the data, but all together it just doesn't insert anything. A thing I forgot to say is that if the destination table is empty it inserts all the rows from the other table, it just doesn't work if there is already data in the table. The destination tabale has no primary key and there is no data type conflict.

[ck9663]

If you run it once and the record gets inserted, the second time you run the same query with the same values, EXCEPT will no longer return anything since you just inserted it. Anyway, if you're still having issue, try to dump it into another table and insert that table. I advice you create a unique index on your target table to avoid duplicate.


Happy Coding!!!


~~ CK

[Elite Hunter]

Thank you CK, creating an unique index worked perfectly. I was trying to add new items, not the ones I already inserted, and the problem was solved with the index.