Click here to Skip to main content
14,975,726 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I have 3 tables. i need to insert 3 column values from first table and 1 column value from second table into 3third table. what is the query for that????
Posted
Updated 2-Nov-11 20:41pm
v2
Comments
Prerak Patel 3-Nov-11 1:15am
   
Without knowing your table structure, it's difficult to help you.

Assuming column1 & column2 are in table1, column3 in table2:

SQL
insert into table3 (field1, field2, field3)
select column1, column2, column3 from table1 t1 join table2 t2 on t1.table_id = t2.table_id
   
Comments
lll1234 3-Nov-11 2:32am
   
I tried like this. Bit getting error


string insert = "insert into table3 (fld1,fld2,fld3) select (fld1,fld2,fld3) from table1 join table2 on table1.id=table2.id";
http://www.java2s.com/Tutorial/Oracle/0080__Insert-Update-Delete/Combinethreetableswithinsertintostatement.htm[^]

Check the link. It creates three tables, populate them and then populate the fourth table from them. This would help you learn how you should proceed.
   
You can use SELECT INTO to do this
Ex:
SQL
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id


Read more about select into here[^]
   
u can something as:
SQL
INSERT INTO NewTable (SELECT tab1_col1, tab2.col2,tab3.col1 FROM   tab1, tab2,tab3 WHERE  Condtion)
   
v3
If yor are directly inserting data i mean if you are passing values as a parameter then you can write direct query like

SQL
INSERT INTO table3(Code) Values ('aaa');

SQL
INSERT INTO MessageStatus (Code) Values ('Status01');

Or you can do following

1)If your new column is static then

SQL
declare @newColID int, @PQID int
Set @newColID = 1
set @PQID =1234

Insert into table3( [PQID] , [col1] , [col2], [newColID])
select PQID,col1,col2, @newColID
from TB1 where TB1.[PQID] = @PQID 

2)If your new column resides in another table(TB2)
If your new column is in a different table (related to TB1 through PQID)

SQL
Insert into table3( [PQID] , [col1] , [col2], [newColID])
select TB1.PQID,TB1.col1,TB1.col2, TB2.newColID
from TB1 INNER JOIN TB2 ON TB1.PQID = TB2.PQID
where TB1.[PQID] = @PQID

Or

SQL
Insert into table3( [PQID] , [col1] , [col2], [newColID])
select TB1.PQID,TB1.col1,TB1.col2, TB2.newColID
from TB1 , TB2
where TB1.[PQID] = @PQID
and TB1.PQID = TB2.PQID



Please let me know if it helps you.

Thanks

Prashant
   
v2
Hi, use below query

SQL
Insert into table3( Field1 , Field2 , Field3)
select TB1.Field1,TB1.Field2,TB1.Field3
from TB1 inner join TB2
where TB1.prKey = TB2.FrKey
   

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