Sunday, June 15, 2008

Finding non-unique rows in SQL Server

I was trying to copy a table of data from Microsoft Access into SQL Server. I setup the MS Access file as a Linked Server and then executed an:

insert into TableName (Col1, Col2, Col3, Col4)
select T.ColA as Col1, T.ColB as Col2, T.Col3, T.Col4,
from ACCESS_DB...TableName T

But I discovered that the unique key had not been set correct on the Access table so I had to find the duplicate keys. This is what did the trick:

select * from ACCESS_DB...TableName where Col1 in(
select Col1 from ACCESS_DB...TableName
group by Col1
having count(Col1) > 1)

This worked from SQL Server directly against the Access DB. The same type of syntax would work against any regular SQL Server table as well.

1 comment:

  1. SELECT
    <columns>
    FROM
    <tableName>
    GROUP BY
    <columns>
    HAVING
    COUNT(nonUnique Column) > 1

    ReplyDelete