Delete Rows with Duplicate Column Data in Specific Columns

Question

I have a table we'll call Table1 with a bunch of junk data in it and no unique identifier column.

I want to select some columns from Table1 and transfer the data to Table2. However, after the transfer, I need to delete rows from Table2 with duplicates in 3 of the columns.

Let's say I have a row in Table2 with columns [FirstName], [LastName], [CompanyName], [City], and [State] that were transferred. I want only the rows with unique combinations of [FirstName], [LastName], and [CompanyName] to remain. To add to the confusion, [LastName] and/or [CompanyName] could contain NULL values. How could I accomplish this? Thanks in advance for any help.

Answer 1

Unique entries can be created using the distinct keyword.

select distinct 
       FirstName, 
       LastName, 
       CompanyName 
  from MyTable

So if you issue the following command, you will only add distinct values to the new table

insert into newTable
(
       FirstName, 
       LastName, 
       CompanyName 
)
select distinct 
       FirstName, 
       LastName, 
       CompanyName 
  from MyTable
 where not exists (
   select 1 from newTable 
    where newTable.FirstName   = MyTable.FirstName
      and newTable.LastName    = MyTable.LastName
      and newTable.CompanyName = MyTable.CompanyName
  ) 

Another nice way to add distinct new values to a table can be done by using the 'MERGE' command.

merge newtable as target
using (select distinct 
              FirstName, 
              LastName, 
              CompanyName 
         from MyTable
       ) as source
   on target.FirstName   = target.FirstName
  and target.LastName    = target.LastName
  and target.CompanyName = target.CompanyName

when not matched by target then
  insert (FirstName, 
          LastName, 
          CompanyName)
  values (target.FirstName, 
          target.LastName, 
          target.CompanyName);

The MERGE command gives you the option to control when you want to synchronize tables.