Violation of PRIMARY KEY constraint on INSERT
Posted on 2006-05-07
I have some tables based on organisations (org_code) ...and I'm trying to copy data from one organisation to another via an INSERT / SELECT query ...
To get a new value for format_id (the primary key), we use another select query to get the current MAX value of the format_id field, then + 1 (So ...SELECT RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE])
I came up with this query to copy the data but it keeps failing saying violation of primary key constraint and I can't figure out why:
INSERT INTO eHR_Accred_Communication_Format
(format_id, format_name, format_description, org_code, isDefault)
SELECT (SELECT RIGHT('000000' + ltrim((cast(isnull(MAX(format_id), 0) AS numeric) + 1)), 6) [NEW_CODE]
FROM eHR_Accred_Communication_Format) AS Expr1, format_name, format_description, '000010' AS Expr2, isDefault
Can anyone see where I'm going wrong?