November 14, 2012 at 11:52 am
I have a database that I attached to SQL Server 2005 from the book "Microsoft ADO.NET 2.0 Step by Step authored by Rebecca Riordan. This database has two tables that each contain a complete set of duplicate rows. The EmployeeList Table has 9 duplicates and the CustomerList Table has 92 duplicates just like someone hit the CREATE TABLE button twice (see atch 1). When I select these 9 duplicate records and try to delete them I get the error message shown in atch 2.
How do I get rid of these duplicates? Thanks, JRichards54 😀
November 14, 2012 at 12:56 pm
jrichards54 (11/14/2012)
I have a database that I attached to SQL Server 2005 from the book "Microsoft ADO.NET 2.0 Step by Step authored by Rebecca Riordan. This database has two tables that each contain a complete set of duplicate rows. The EmployeeList Table has 9 duplicates and the CustomerList Table has 92 duplicates just like someone hit the CREATE TABLE button twice (see atch 1). When I select these 9 duplicate records and try to delete them I get the error message shown in atch 2.How do I get rid of these duplicates? Thanks, JRichards54 😀
This would be a lot simpler for people to respond if you had included ddl and sample data. Graphic attachments do little in the way of making it easy for somebody to help. I didn't feel like typing in all the data so I just created the first 3 in my sample.
create table #EmployeeList
(
EmployeeID int,
LastName varchar(50),
FirstName varchar(50)
)
insert #EmployeeList
select 1, 'Davolio', 'Nancy' union all
select 2, 'Fuller', 'Andrew' union all
select 3, 'Leverling', 'Janet' union all
select 1, 'Davolio', 'Nancy' union all
select 2, 'Fuller', 'Andrew' union all
select 3, 'Leverling', 'Janet'
select * from #EmployeeList --just to see the contents of the temp table
;with cte as
(
select EmployeeID, LastName, FirstName, ROW_NUMBER() over (PARTITION BY LastName, FirstName order by EmployeeID) as RowNum
from #EmployeeList
)
delete cte where RowNum > 1 --This will keep only the "first" version of each set of values
select * from #EmployeeList --Now we can see the duplicates were removed
drop table #EmployeeList
There are other ways to do this but this is one of the simplest ways I have found.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2012 at 3:03 pm
Thank you SSCertifiable, this script worked just fine. It removed the 9 duplicate rows in the EmployeeList Table.
You wrote: "This would be a lot simpler for people to respond if you had included ddl and sample data. Graphic attachments do little in the way of making it easy for somebody to help." Please explain what you mean by "ddl and sample data". Please forgive my ignorance but I am trying hard to post properly and I just do not know what you want. When I made that last post I had not written the script yet to try to delete those duplicates so I did not have any code to post. Enlighten me Sir and I will surely do much better on my next post. Thank you again for your excellent help. JRichards54
November 14, 2012 at 3:47 pm
Read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply