x

how to insert data from multiple table into one table

table1 tbl_ItemCreation:(itemCreation_id primary key and autogenerated)

 Item_Name varchar(50),
 Item_Code  varchar(50),
 Group_Name varchar(50),
 UnderGroup varchar(50),
 Unit_Name varchar(50), 

table 2 tbl_ItemGroup(itemGroup_id primary key and autogenerated)

 Group_Name varchar(50),
 Chbox_Val varchar(50), 
 UnderGroup varchar(50),
 Comments varchar(50)

table 3 tbl_UnitCreation:(Unit_id primary key and autogenerated)

 Unit_Name varchar(50),
 Unit_Abbreviation varchar(50),
 Unit_type varchar(50)

these are my 3 tables.and id of each table is autogenerated. i want to insert group_name,undergroup from table 2 i.e tbl_ItemGroup into table 1 i.e tbl_ItemCreation.

also unit_name from table 3 i.e tbl_UnitCreation into table 1 i.e tbl_ItemCreation.

i am confused over table 1 i.e tbl_itemCreation that should i use foreign key or not? and how.

also exactly which method to use for this.

i tried this by using joins.

 INSERT INTO tbl_ItemCreation
 SELECT @Item_Name,@Item_Code,t1.Group_Name,t1.underGroup ,t2.unit_Name,@Comments1,t1.ItemGroup_Id
 FROM tbl_ItemGroup t1
 Inner JOIN tbl_ItemCreation t3 on t1.ItemGroup_Id=t3.ItemCreation_Id 
 inner join tbl_UnitCreation t2 on t3.ItemCreation_Id=t2.Unit_Id

it is not producing error.but not giving expected result also. m i doing wrong?

more ▼

asked Apr 04, 2016 at 11:26 AM in Default

avatar image

ssk
1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

In this case, you have to get unitname, groupname of the parent tables by passing the unitid, groupid for the item.

It is preferrable to have the unitid, groupid in the itemcreation table, instead of unitname, groupname. It will avoid UPDATE anomalies in future. You have to go for Normalized design.

You are trying to use child table(itemcreation) in JOINS to insert value into child table(itemcreation) and it is not right here.

more ▼

answered Apr 05, 2016 at 04:24 AM

avatar image

Venkataraman
1.4k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x379
x162
x39
x12

asked: Apr 04, 2016 at 11:26 AM

Seen: 64 times

Last Updated: Apr 05, 2016 at 04:24 AM

Copyright 2017 Redgate Software. Privacy Policy