How to select few columns from multiple table and insert into one table.

Question

0.00/5 (No votes)

See more:

i have 3 tables
itemcreation table
Item_Name varchar(50),
Item_Code varchar(50),
Group_id int,(foreign key)
Unit_id int (foreign key)

itemgroup table
Group_Id int (primary key)
Group_Name varchar(50),
Chbox_Val varchar(50),
UnderGroup varchar(50)

Unit table
Unit_Id (primary key)
Unit_Name varchar(50),
Unit_Abbreviation varchar(50)

i want to insert Group_name,Undergroup from table itemgroup into table itemcreation
also unit_name from unit table into itemcreation table.

What I have tried:

i did this:
INSERT INTO tbl_ItemCreation SELECT @Item_Name,@Item_Code,t1.Group_Name,t1.underGroup ,t2.unit_Name
FROM tbl_ItemGroup t1,tbl_Unit t2
Inner JOIN tbl_ItemCreation t3 on t3.ItemCreation_Id=t1.ItemGroup_id
Inner Join tbl_ItemCreation t3 on t3.ItemCreation_Id=t2.Unit_Id

but getting error

Posted 2-Apr-16 3:42am

Member 12385326

Updated 2-Apr-16 4:34am

Add a Solution

Comments

Karthik_Mahalingam 2-Apr-16 10:03am

what error?

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)

CHill60 · Answer 1 · 2016-04-02T04:34:00

The errors I got when trying to run this, and how I fixed each one:

1. Invalid object name 'tbl_ItemGroup' (et al)
This is entirely down to the way you have described your problem. I foolishly assumed you had used the real table names when you gave the schema. I had already created the tables so any sql posted below will be missing the "tbl_" on the table names.

2. The multi-part identifier "t1.ItemGroup_id" could not be bound.
Again, down to the schema you provided. In any sql below that column will be called Group_id

3. The correlation name 't3' is specified multiple times in a FROM clause.
This is a real problem. You can't use the alias t3 twice. So you either meant

C#

Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
Inner Join ItemCreation t4 on t4.ItemCreation_Id=t2.Unit_Id

or probably more likely meant

C#

Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
                AND t3.ItemCreation_Id=t2.Unit_Id

4. The multi-part identifier "t1.Group_Id" could not be bound.
At first I thought this was just down to the differences between the schema and the query you posted but then I spotted that you hadn't joined to Unit correctly
You need to use JOIN rather than the old syntax you have used. Otherwise you will need a WHERE clause to define how Unit should link to one of the other tables. So I looked again at the lines I mentioned in point 3 and concluded that what you actually meant was

SQL

INSERT INTO ItemCreation
SELECT @Item_Name,@Item_Code,t1.Group_Name,t1.underGroup ,t2.unit_Name
FROM ItemGroup t1
Inner JOIN ItemCreation t3 on t3.ItemCreation_Id=t1.Group_Id
inner join Unit t2 on t3.ItemCreation_Id=t2.Unit_Id

5. Not a syntax error here but you have no WHERE clause on this query - which means it is going to insert a new row for every row that is already on the table - I'm not sure that's what you really want to do either.

Get your errors fixed and try running your query several times. Check the behaviour is really what you want. If not then by all means come back with some sample data and an expected result.