prevent to not copy duplicate data from another table

Question

0.00/5 (No votes)

See more:

I have a table
Hadiths_Book
which include column
Book_Id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name

now i am inserting data from another table name Hadiths
using this query

SQL

insert into Hadiths_Books (Book_Id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name) 
select distinct ((select MAX(Book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY hadith_eng_book)) as Book_Id,
 hadith_book_name, hadith_book_name, CAST(hadith_eng_book as varchar(50)) as hadith_eng_book from Hadiths

but the problem is all duplicate data also inserting into my table while i have use distinct keyword but no progress.

Posted 30-Sep-13 6:47am

Muhamad Faizan Khan

Add a Solution

Comments

jeenamary 30-Sep-13 13:41pm

what all are fields , which you are using to determine an entry is a duplicate entry?

2 solutions

Solution 1

First, your problem: you're selecting "DISTINCT" but it has no relationship to the target table: nothing is there to stop it from adding entries already present.

You could use a subquery "WHERE [some field] IS NOT IN some query against your target table. That depends upon whether our not your data for each book is unique with respect to [some field].

You may wish, instead, to create a UNION of the two tables - and leave out the ALL keyword then you will only get one copy of each. Beware, however, that any difference two entries will cause them both to appear.

Then, if you've got the UNION working OK, use it t select into a table and you'll have all of your unique entries.

Really, the same problem as before.

Posted 30-Sep-13 8:30am

W∴ Balboos, GHB

Updated 30-Sep-13 8:31am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Azee** · Accepted Answer · 2013-09-30T09:45:00

Hello there,

It seems, in this part of the statement,

SQL

select distinct ((select MAX(Book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY hadith_eng_book))

you are trying to generate an ID that is one greater that the last ID in the table.

So instead of doing that, why don't you set the Identity Specification of Column BookID of the Table to Yes that way you won't have to generate the ID, it'll increment itself and you can just use distinct to get non-duplicate records and insert them like this:

SQL

insert into Hadiths_Books ( Book_Arabic_Name, Book_Urdu_Name, Book_English_Name) 
select distinct 
 hadith_book_name, hadith_book_name, CAST(hadith_eng_book as varchar(50)) as hadith_eng_book from Hadiths

A little update, I found a solution to remove duplicates with your current scenario. Now I don't know what is the primary key column of Hadiths Table, so I have used HadithsID, here it goes:

SQL

insert into Hadiths_Books (Book_Id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name) 
select A.Book_Id, A.hadith_book_name,A.hadith_book_nameUrDu, A.hadith_eng_book  from (
select (select MAX(Book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY hadith_eng_book) as Book_Id,
 hadith_book_name, hadith_book_name as hadith_book_nameUrDu, CAST(hadith_eng_book as varchar(50)) as hadith_eng_book
   ROW_NUMBER() OVER (PARTITION BY hadith_book_name,hadith_eng_book ORDER BY HadithsID) AS RowNumber
  from Hadiths ) as A where A.RowNumber = 1

I tested a similar scenario on my own table, and just merged the solution in your query, so there might be some syntax error, but it works.

Hope it helps, let me know how it goes.

Good luck.

Azee...