Solved

REMOVING DUPLICATE VALUES FROM MY DATA FOR AN INSERT

Posted on 2008-06-26
3
198 Views
Last Modified: 2010-03-20
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.

Using:
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?
0
Comment
Question by:scm0sml
  • 2
3 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21873579
you are checking the data from your temp table which may have unique airline names. however since you are inserting into table AIRLINE , you need to check if there are any existing records (by airline name) that appear in both tables.
below will return records from your temp table whose name already appears in your airline table:

select t.*
from AirlineCabinClass$ t join AIRLINE a
on t.name = a.airlinename
0
 

Author Comment

by:scm0sml
ID: 21874304
no there are not it is an empty table

0
 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21879922
if you want to find dupes by name field in your table then
select [Name], COUNT([CarrierCode]) as x
from AirlineCabinClass$
GROUP BY [NAME]
having COUNT([CarrierCode]) > 1
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question