REMOVING DUPLICATE VALUES FROM MY DATA FOR AN INSERT
Posted on 2008-06-26
TicketingCode Name CarrierCode ClassCode CabinDescription
121 ADA Air ZY A First
121 ADA Air ZY B Economy
121 ADA Air ZY C Business
I have the above data in a temporary table.
The first thing I need to do is insert the distinct ticketing code, name and carrier code into a table called airline.
INSERT INTO AIRLINE(AIRLINENAME,TICKETINGCODE,REFCODE)
select distinct [Name], TicketingCode, CarrierCode from AirlineCabinClass$
However the airlinename field is a unique field and im getting the error:
Cannot insert duplicate key row in object 'dbo.AIRLINE' with unique index 'UNC_Airline_AirlineName'.
I need some debug sql to try and show me the duplicate name. Please be aware that the data above is a sample and there are a lot more records for that airline and also many other airlines with a similiar amount of classes.
I was trying something like:
select DISTINCT [Name], CarrierCode, COUNT([NAME]) from AirlineCabinClass$
GROUP BY [NAME], TicketingCode, CarrierCode
but count(name) is given me 26 for the example above as there are 26 records for that airline name.
Im assuming that there will be an airline name that has two carriercodes or ticketing codes hence leaving 2 names the same in my distinct list.
Can anyone help?