Attack of the Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Attack of the Duplicates

Author

Topic

Drummos
Starting Member

4 Posts

Posted - 2007-02-11 : 14:48:50

Help, lol. I recently imported all my table data into a new database on a new Win Server 2003 from SQL 2005 to SQL 2005. I went in to set my PK on the new tables.

Come to find out, all my records were duplicated. So, I followed the advice in an article on this forum about deleting duplicate records.

That worked. But, the duplicate records refuse to be beaten so easily. Every time I go in to create my PK a new single duplicate appears. I'm not sure what is happening but all my tables are experiencing this issue.

If anyone can help me out I'd be eternally grateful.

Thanks,

Ally

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-11 : 15:25:06

Have you run the SQL statement that lists the dupe records? Something like:

SELECT val1,
COUNT(val1) AS Kount
FROM table_name
GROUP BY val1
HAVING (COUNT(val1) > 1)

Since you haven't given us any details about your table, you are going to have to substitute the proper column and table names into the query.

This will show you the dupe values that you will need to fix.

Also, keep in mind that you cannot have NULLs in the column you are trying to apply the PK to. So, be sure that this issue is not occuring becuase you have a single NULL somewhere.

-ec

Drummos
Starting Member

4 Posts

Posted - 2007-02-11 : 16:07:20

You know I did do that but for the sake of double checking I did it again. This time I found three duplicate records, deleted them, and I did not have any problems and could set the key. Go figure. Maybe I just needed to establish a fresh connection. I logged off earlier in frustration.

It was just wierd. Thanks, I'll give this another whirl. Wish me luck.

Ally

Drummos
Starting Member

4 Posts

Posted - 2007-02-11 : 16:12:57

OH! You know what I think I know what caused my problem. I took a drive out to the office to see if what I was experiencing was some strange side effect caused by logging in remotely. It wasn't, the records were duplicated on the server.

However, I found that one of my employees was logged onto the local server, probably doing some work in the database. He did not log off and the server was locked. I bet it was preventing me from making changes. I logged him off before I left. I bet that was the problem. :)

Ally

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources