how to insert data from another table while data mismatch and primray key problem

Question

0.00/5 (No votes)

See more:

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 29-Sep-13 4:21am

Muhamad Faizan Khan

Updated 29-Sep-13 4:23am

v2

Add a Solution

1 solution

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)

Jorge J. Martins · Accepted Answer · 2013-09-29T05:26:00

Solution 1

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.

Posted 29-Sep-13 5:26am

Jorge J. Martins

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"

Jorge J. Martins 29-Sep-13 12:25pm

Try to enclose it in ().

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

This as been working well in SQL Server 2008 R2.

Jorge J. Martins 29-Sep-13 12:41pm

I tried your query and there is a problem with the parenthisis count.
Here is the one that works.

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:53pm

rate5

Muhamad Faizan Khan 29-Sep-13 12:54pm

having problem now when using distinct keyword in subquery

Jorge J. Martins 29-Sep-13 12:55pm

What subquery?

Muhamad Faizan Khan 29-Sep-13 13:01pm

from another table I want to insery distinct data. when I write distinct ketyword in select query it showing me error

Jorge J. Martins 29-Sep-13 13:06pm

You mean:

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 Book_English_Name) as Book_Id, hadith_book_name, CAST(hadith_eng_book as varchar(50)) as Book_English_Name from Hadiths

I tried it and it works.

Muhamad Faizan Khan 29-Sep-13 13:11pm

oh sorry i was using distinct keyword before hadith_book_name