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