防止不从另一个表复制重复数据 [英] prevent to not copy duplicate data from another table

问题描述

我有一张桌子

Hadiths_Book

包括栏目

Book_Id，Book_Arabic_Name，Book_Urdu_Name，Book_English_Name



现在我正在使用此查询从另一个表名Hadiths插入数据

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

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

但问题是所有重复数据也插入到我的表中，而我使用了distinct关键字但没有进展。

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

推荐答案

首先，你的问题：你选择了DISTINCT，但它与目标表没有任何关系：没有什么可以阻止它添加已经存在的条目。





您可以使用子查询WHERE [某个字段]不是针对您的目标表的某些查询。这取决于我们的每本书的数据是否与[某些领域]无关。





您可能希望，而不是，要创建两个表的 UNION - 并且省略ALL关键字，那么每个只能获得一个副本。但要注意，两个条目之间的任何差异都会导致它们出现。



然后，如果你让UNION工作正常，请使用它选择一个表格，你将拥有所有独特的条目。



真的，和以前一样的问题。

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.

你好，



看来，在声明的这一部分，

Hello there,

It seems, in this part of the statement,

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

你正在尝试生成一个比表中最后一个ID大一个的ID。



所以不要这样做，为什么不呢？设置身份规范表 BookID 表是这样你不必生成ID，它会自行增加你可以只使用 distinct 获取非重复记录并按如下方式插入：

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:

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

稍微更新，我找到了一个解决方案，可以删除当前场景中的重复项。现在我不知道什么是Hadiths Table的主键列，所以我使用了HadithsID，这里是：

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:

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

我在自己的桌子上测试了类似的情况，并且刚刚合并了你的解决方案查询，所以可能有一些语法错误，但它的工作原理。





希望它有所帮助，让我知道它是怎么回事。< br $> b $ b

祝你好运。



Azee ......

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...

这篇关于防止不从另一个表复制重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！