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.
SELECT
ReplyDelete<columns>
FROM
<tableName>
GROUP BY
<columns>
HAVING
COUNT(nonUnique Column) > 1