How to rename duplicate rows in SQL Server 2005?

Question

So here is my problem. For my class we had to import a .xls file into our SQL database. The .xls files contains fictional information regarding a fictitious company's accounts. After importing the .xls file, I tried to establish a primary key and received an error of having duplicate rows, thus it wouldn't establish the PK. So I wrote up a quick script to search and display all duplicate rows and it listed 80 rows in the results.

Now I need to rename the rows because each duplicate row contains different information from the 'original' row.

Ex (Table contains: Account, Description, and Short Description):

430001 | Rustic Baguette | La Jolla Bakery Department Sales Returns and Allowances
430001 | Rustic Baguette | Del Mar Bakery Department Sales Returns and Allowances

Note how the account numbers are the same, but the information within each row is different.

So onto my question...is there a way to rename the duplicate rows without having to do all 80 of them manually using the UPDATE statement (mind you I am still learning this).

Answer 1

Assuming you don't care about the value of the existing account number and just need each one to be unique, you can do this:



Create a new temp table which has an auto identity column. Insert all you records (minus the existing account number) into the this table and each record will get a unique number.



Then, delete all the records from your original table and finally insert the contents of the temp table back into your original table



CREATE TABLE #Temp

(

Account INT IDENTITY(1,1),

Desc VARCHAR(500),

ShortDesc VARCHAR(500)

)



INSERT INTO #Temp

SELECT Desc, ShortDesc FROM YourTable



TRUNCATE TABLE YourTable



INSERT INTO YourTable

SELECT * FROM #Temp



DROP TABLE #Temp



ALTER TABLE YourTable ADD CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED

(

[Account] ASC

)

Answer 2

The problematical area approximately duplicate removing is keeping merely between the duplicates while there is not a thank you to tell apart between them. between the fewer blunders-companies thank you to realize this is to make it so which you would be able to tell them aside: upload a column to the table and make it autonumber or something comparable. Then, you are able to create an easy technique alongside the strains of CREATE CURSOR delete_targets AS elect id, fname, lname, MIN(newField) AS keepValue FROM someTable group by skill of id, fname, lname HAVING count sort(*) > a million Then, fetch each and each row of the delete_targets into @id, @fname, @lname and @keepValue and then: DELETE FROM someTable the place id = @id AND fname = @fname AND lname = @lname AND newField > @keepValue when you're carried out, drop the hot column.

Answer 3

Which is the column you are trying to set the Primary Key constraint?