Insert Query error 1

[pmsbony]

I am trying to insert about 90,000 records from a table into another table with the following query:


insert into areacode(area_code, area_county, area_town, area_dep_locality,
area_locality, area_dep_street, area_street, area_la_name, area_easting, area_northing)
select acode3005.area_code, acode3005.area_county, acode3005.area_town,
acode3005.area_dep_locality, acode3005.area_locality, acode3005.area_dep_street,
acode3005.area_street, acode3005.area_la_name, acode3005.area_easting, acode3005.area_northing
from acode3005 left outer join areacode
on acode3005.area_code = areacode.area_code

I am always getting the following error:


Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'AreaCode' with unique index 'XIE1AreaCode_AreaCode'.
The statement has been terminated.

I have previously run a query on the acode3005 table to delete any records that existed (in terms of the area_code field) in areacode, the error suggests to me that I have not deleted all the matching records.

 

[rajeevnandanmishra]

Hi there,
What you are doing is trying to insert in areacode only those record which matches to areacode field in acode3005.

Now just suppose you have two records in areacode table with areacode 1 and 2. And these records exist in acode3005 also. Your query will always try to insert the same area code again and again because of the condition
from acode3005 left outer join areacode
on acode3005.area_code = areacode.area_code

What i think you are requiring to insert only those records which are in acode3005 and not in areacode. It it is true, here is your answer.
------------------------------------
insert into areacode(area_code, area_county, area_town,
area_dep_locality,area_locality, area_dep_street,
area_street, area_la_name, area_easting, area_northing)
select area_code, area_county, area_town,
area_dep_locality, area_locality, area_dep_street,
area_street, area_la_name, area_easting, area_northing
from acode3005
where area_code not in
(select area_code from areacode)
------------------------------------

Let me know if it does not help.