Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP
--sql