Click here to Skip to main content
15,881,139 members
Search within:


Index use to how to avoid duplication
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have one table when i am importing excel file into this table how to avoid dupliaction using index.

SQL
CREATE UNIQUE INDEX MyIndex
ON Dataupload(date,time,BREMPROCODE,BREMPRONAME,PRICE)
WITH IGNORE_DUP_KEY

this code is not working correctly
Posted
Updated 5-Jul-15 23:33pm
v2
Add a Solution
Comments
Abhipal Singh 6-Jul-15 5:42am    
To avoid duplication of data, you can have a primary key for each row. Indexes are not meant for this purpose
Unni R 6-Jul-15 6:28am    
hourly data importing to table so how can avoid duplication .

1 solution

Well if you do not want your index to be unique, remove the 'UNIQUE' keyword.

If you add a constraint to be unique, that contraint will cause a violation if you attempt to insert identical values.

Where you can do is to avoid the duplicates in the select statements inserting the data. You should add an index if you want quick access to rows based on the values in the index only.

For instance like this:

SQL
INSERT INTO DATAUPLOAD(DATE, TIME, BREMPROCODE, BREMPRONAME, PRICE)
(SELECT DISTINCTROW DATE, TIME BREMPROCODE, BREMPRONAME, PRICE FROM ACMEDATASOURCETABLE)


in the case of hourly insertions where you could have the value already present, you need to use for instance an outer join also:

SQL
INSERT INTO DATAUPLOAD(DATE, TIME, BREMPROCODE, BREMPRONAME, PRICE)
(SELECT DISTINCTROW DATE, TIME BREMPROCODE, BREMPRONAME, PRICE FROM ACMEDATASOURCETABLE A LEFT JOIN DATAUPLOAD B ON A.DATE = B.DATE AND A.TIME = B.TIME AND A.BREMPROCODE = B.BREMPROCODE AND A.BREMPRONAME = B.BREMPRONAME AND A.PRICE = B.PRICE WHERE B.BREMPROCODE IS NULL)


If cause this is not an overly effective way to do this, but it's pretty simple, that last B.BREMPROCODE could of cause be any field, just checking that joining the values back at the table to insert into produces an empty value indication.

Se more exaples of outer joins here: https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx[^]
 
Share this answer
 
Posted
Updated 6-Jul-15 1:48am
v2
Comments
deepankarbhatnagar 6-Jul-15 6:48am    
Hey your solution will not remove or avoid redundency..
Thomas Nielsen - getCore 6-Jul-15 7:35am    
no, doesn't relate to redundancy. The question was how to avoid duplicates. Redundancy refers to duplicating data on each row which could be normalized into seperate tables to provide a smaller row with just an id key as a reference e.g. This way data would only be there once, in the referred row.... but that's not at all what the question is about. Unni want's to use an index to avoid having to do anything else and still avoid duplicate entries.
In this case, the constraint of uniqueness on an index can ensure that, but it's not the purpose of an index or a constraint, which i try to examplfy then is, another way to ensure that the data being inserted is unique which was his objective.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to avoid inserting duplicate Access dataBase in tables using VB.net
How to avoid duplicate data in SQL
How to avoid duplicate insert into database?
How to avoid duplicate entry into database
How to avoid duplicate random questions
How to avoid duplicate value
How to avoid the duplicate values
Binary search with duplicates in a sorted array
how to avoid duplication in add query in c#.net
tuning htAccess to avoid duplicated content and avoid problems with google panda & co?

Advertise
Privacy
Cookies
Terms of Use
Last Updated 6 Jul 2015
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900