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))

[MAK]

execute
sp_help tablename


find the primary key constraint and its attributes.

[JuanCarlos]

CNUECOD is primary key in VTA020SAP2 table

[MAK]

is there any other unique index you have on that table?

[JuanCarlos]

nop

[MAK]

export that table and all related tables to a new database and take backup of that database and post it here or email me. I will take a look.

[JuanCarlos]

THX MAK!!

The original database is in SQL Server, i exported the tables to Access and attached to this post. Just a few records.


TIA

Regards

[MAK]

CNUECOD is the primary key column.
PK_VTA020SAP2 is the constraint name for that primary key column.

When you are inserting try to insert rows that do not match the CNUECOD.

eg: Add this statement in your query.

and VTA020SAP2.CNUECOD not in (select CNUECOD from VTA020SAP2)

[JuanCarlos]

Thx again MAK, but, could you post the entire SQL sentence please.


Regards

[MAK]

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))

and VTA020SAP2.CNUECOD not in (select CNUECOD from VTA020SAP2)

[JuanCarlos]

The column prefix 'VTA020SAP2' does not match with a table name or alias name used in the query.

[MAK]

try this.

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
and VTA020SAP1.CNUECOD not in (select CNUECOD from VTA020SAP2)

[JuanCarlos]

it don't work when vta020sap2 is empty.

same error with the primary key

[MAK]

Please backup the database and zip it and post it here. Let me take a look.

[JuanCarlos]

The original DB is sql, i exported to access just a few records


Regards