key violation with append query

[LuigiX]

Hi

I'm trying to add records to the asset_details table. Basically for every record that exists for a Switch (SWIT) currently I want to create a corresponding record for the parent asset, a switch site (SWSI).

The primary key on the asset_details table is the Asset_ID so I would not expect the key violation error since I'm simply trying to replace the child asset (SWIT) with its parent asset ID for the new record.

I've attached the database to this thread which includes my feeble attempt at the append query - "Insert_Query" which is as follows:

INSERT INTO asset_details ( Asset_ID, Detail_Code, Detail_Text, Detail_Date )
SELECT asset_parent.Parent_Asset, asset_details.Detail_Code, asset_details.Detail_Text, asset_details.Detail_Date
FROM asset INNER JOIN (asset_parent INNER JOIN asset_details ON asset_parent.Asset_ID = asset_details.Asset_ID) ON asset.Asset_ID = asset_details.Asset_ID
WHERE (((asset_details.Detail_Code)='SWMT') AND ((asset.Asset_ID)=[asset_parent].[Asset_ID]));


Any help is much appreciated

Cheers

Luigi

 

[namliam]

Your inforcing referential integrity, this is causing a problem. Turn that off and no more error.... So check that out....

Futhermore your query seems strange.

INSERT INTO asset_details ( Asset_ID, Detail_Code, Detail_Text, Detail_Date )
SELECT asset_parent.Parent_Asset, asset_details.Detail_Code, asset_details.Detail_Text, asset_details.Detail_Date
FROM asset_parent INNER JOIN (asset INNER JOIN asset_details ON asset.Asset_ID = asset_details.Asset_ID) ON asset_parent.Asset_ID = asset_details.Asset_ID
WHERE asset_details.Detail_Code='SWMT';

Will do the same but is a little better

Regards

 

[LuigiX]

Hi

Thanks however I cant turn ref integrity off. It is third party software that cant be altered.

I cant understand that even when I'm putting unique keys into asset_details, the id of the parent asset, I still cant do this.

I have had success with one record however.

Cheers

Luigi

 

[Pat Hartman]

The structure does not make any sense to me. I can't figure out why you would need the Asset_parent table since parent field could be stored in the Asset table. Unless there is a many-to-many relationship between assets and parents. But if that were the case, the pk of the Asset_parent table would need to include BOTH Asset_ID and Parent_Asset.

The Asset_details table does not have a primary key (I can't imagine why since ALL tables should have primary keys) so I don't know why you are getting key violations when you insert into that table.