What is wrong with this sentence??

[JuanCarlos]

CNUECOD is primary key in VTA020SAP2 table

TIA

Violation of PRIMARY KEY constraint 'PK_VTA020SAP2'. Cannot insert duplicate key in object 'VTA020SAP2'.
The statement has been terminated.

INSERT INTO VTA020SAP2 SELECT VTA020SAP1.*, NULL as xCLIPRO, NULL AS xPAIS, NULL AS xCONFREI, NULL AS xCONCPER,
NULL AS xMONEDA, NULL AS xFAX, NULL AS xTIPEXP, NULL AS xCASILLA, NULL AS xDIAENTR, NULL AS xPUNVEN,
NULL AS xCIUDAD, NULL AS xREGION, NULL AS xCONDIC, NULL AS xINSPAG, NULL AS xCUMPLE,
NULL AS xPERVIS, NULL AS ESTADO FROM dbo.VTA020SAP1
INNER JOIN VTA020
ON VTA020SAP1.CNRCLI = VTA020.CNRCLI
AND VTA020SAP1.CNRCLI IN (SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1
GROUP BY VTA020SAP1.CNUECOD)
AND (not EXISTS
(SELECT VTA020SAP2.* FROM dbo.VTA020SAP2
INNER JOIN VTA020SAP1 ON VTA020SAP1.CNUECOD = VTA020SAP2.CNUECOD
WHERE dbo.VTA020SAP2.CNUECOD = dbo.VTA020SAP1.CNUECOD))

[skhanal]

You may have to change this statement

SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1
GROUP BY VTA020SAP1.CNUECOD

to

SELECT MAX(A.CNRCLI) FROM VTA020SAP1 A
where VTA020SAP1.CNUECOD = A.CNUECOD

But again if you have two or more records in VTA020SAP1 with same CNRCLI then you will still get the error.

[JuanCarlos]

Originally posted by skhanal
You may have to change this statement

SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1
GROUP BY VTA020SAP1.CNUECOD

to

SELECT MAX(A.CNRCLI) FROM VTA020SAP1 A
where VTA020SAP1.CNUECOD = A.CNUECOD

But again if you have two or more records in VTA020SAP1 with same CNRCLI then you will still get the error.

THX but it insert only one row we have 7000 rows in VTA020SAP1 and 7000 in VTA020