Click here to Skip to main content
15,882,063 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i want to insert data into my table from a different table but the problem is some of the data type are not matching plus some of the column's value not available in the another table.

look this

this is the table where i want to add data from another table
SQL
insert into Hadiths_Books (book_id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name)


here book_id is primary key which is not available into another table from where i am coping data.
plus book_english_name data type is varchar(50) but the table from where i am coping is nvarchar(50).
now how to resolve
this. urgent i cant make it again
Posted
Updated 29-Sep-13 4:23am
v2

1 solution

The datatype problem is solved with
SQL
CAST(Book_English_Name as varchar(50)) as Book_English_Name


Now, for the primary key problem. I suppose it's not an auto-increment column or it wouldn't be a problem.
What you need to do, before the insert, is finding out the MAX(book_id) and increment each insert with ROW_NUMBER from your source table.

You'd have:
SQL
insert into Hadiths_Books (book_id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name)
select ((select MAX(book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY Book_English_Name), Book_Arabic_Name, Book_Urdu_Name, CAST(Book_English_Name as varchar(50)) as Book_English_Name) from foreign_table


The (ORDER BY Book_English_Name) can be ordered by anything from the foreign_table.

Don't forget to mark it as answered if it's the case.
 
Share this answer
 
Comments
Muhamad Faizan Khan 29-Sep-13 11:47am    
Thank You Very Much as i will do practical of it i'll rate it.;-)
Muhamad Faizan Khan 29-Sep-13 11:57am    
OVER(ORDER BY Book_English_Name), For What ?? and here at Comma "," Showing me error
incorrect syntax here
Muhamad Faizan Khan 29-Sep-13 12:03pm    
insert into Hadiths_Books (Book_Id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name)
select ((select MAX(Book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY Book_English_Name), hadith_book_name, CAST(hadith_eng_book as varchar(50)) as Book_English_Name) from Hadiths

this is the query
but showing me 2 errors
one for command at here-> OVER(ORDER BY Book_English_Name),
and second for bracket at here-> as Book_English_Name)
Jorge J. Martins 29-Sep-13 12:09pm    
Try this

insert into Hadiths_Books (Book_Id, Book_Arabic_Name, Book_Urdu_Name, Book_English_Name) select ((select MAX(Book_id) from Hadiths_Books) + ROW_NUMBER() OVER(ORDER BY Book_English_Name) as Book_Id, hadith_book_name, CAST(hadith_eng_book as varchar(50)) as Book_English_Name from Hadiths
Muhamad Faizan Khan 29-Sep-13 12:20pm    
really sorry now error at here
OVER(ORDER BY Book_English_Name) as Book_Id,
error! Incorrect Syntax near the keyword "as"

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



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