• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Same ID displayed multiple times in SQL Server

I've imported a table from MS Access to SQL Server. For some reason each record is displayed four times. What can I do in order to have them displayed only once?
Thank you for your help
0
baxtalo
Asked:
baxtalo
  • 6
  • 5
1 Solution
 
TheAvengerCommented:
Do you have a primary key defined? Or is the ID column are you talking about supposed to be the primary key?
0
 
TheAvengerCommented:
By the way, if you just want to show every record a single time, you can add the DISTINCT keyword:

SELECT DISTINCT *
FROM TableName

You can also show your query so that we can have more details
0
 
baxtaloAuthor Commented:
I used
SELECT DISTINCT * FROM
on my web page and it worked. Now I can see each record only once on my web page.
But I was wondering if I could do the same in the database.
I don't have a primary key defined, the ID column is bigint and it doesn't allow nulls.
I tried to set the ID column primary key, but I get the error "unable to create index because a duplicate key was found.... The duplicate key value is (3)
I don't know what this means.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
TheAvengerCommented:
It means exactly the same: that you have multiple times the same value.

You can execute this in SQL Server Enterprise Manager (replace YourTable with the name of the original table):

SELECT DISTINCT * INTO TempTable FROM YourTable

If successful, check the result by doing:

SELECT * FROM TempTable

If everything is OK, execute:

DELETE FROM YourTable

If everything is OK, execute:

INSERT INTO YourTable SELECT * FROM TempTable

If everything is OK, execute:

DROP TABLE TempTable

That should fix the data in the original table
0
 
baxtaloAuthor Commented:
Everything was good until I got to
INSERT INTO YourTable SELECT * FROM TempTable
At this point this error message was displayed: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'YourTable' can only be specified when a column list is used and IDENTITY_INSERT is ON
0
 
baxtaloAuthor Commented:
but it also said 'Query completed with errors'
Shall I still proceed to the DROP TABLE ?
0
 
baxtaloAuthor Commented:
OK, I've lost all my data, but no problem, I still have the entries in the Access database. I'm going to import it again.
Thank you for your help, I've learned a lot.
0
 
TheAvengerCommented:
No, the table TempTable now contains your data.

Execute this:

SET IDENTITY_INSERT YourTable ON
INSERT INTO YourTable SELECT * FROM TempTable
SET IDENTITY_INSERT YourTable OFF

Execute every line separately and check if everything is OK. If it is OK, make a:

SELECT * FROM YourTable

Drop TempTable only when you see the data correctly in YourTable
0
 
TheAvengerCommented:
You were faster than my "Don't do it" answer :-( Sorry
0
 
baxtaloAuthor Commented:
No problem, I'm doing it from the beginning now. I really enjoy it.
It's my second day when I'm working with SQL Server. It's pretty cool...
0
 
TheAvengerCommented:
Yeah, much better than Access ;-)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now